Search code examples
excelexcel-formulacountifsumifs

How to calculate no of days where sales were made in MS excel using sumifs and countifs?

I am working on an excel sheet where I am required to calculate average number of days the stores in a city were able to make some sales. I am attaching a sample of the table for reference. The values in the cells represent the number of units sold(not relevant to this question).

enter image description here

Here across NY, two stores are present, and out of the total number of days in consideration (3*2), only 4 days some sales were made, making the average 66%. Similarly for Paris, there exists only one store which was open across all days.

To arrive at the figures, I tried using nested countifs and SUMIFS , but did not receive the expected results. Also, in some of the older posts, users had suggested to use INDEX MATCH with SUMIFS, but I was not to get accurate results using these.

Can anyone help me to get the correct figures for Total days, and Days with some sale.

Solution

  • SUMPRODUCT SOLUTION

    =SUMPRODUCT(--(A$2:A$5=A8)*--(C$2:E$5<>""))
=SUMPRODUCT(--(A$2:A$5=A8)*--(C$2:E$5<>"NO SALE"))
=ROUND(C8/B8,4)

    enter image description here