Averages of the past 3 months with SUMIFS & COUNTIFS

Occasional Contributor

Rosa13055_0-1660815781812.png

I am trying to set up calculated fields for the averages for the past 3 months on my excel dashboard. 

=(SUMIFS(Sales_Data!H:H,Sales_Data!B:B,$G$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+SUMIFS(Sales_Data!H:H,Sales_Data!B:B,$H$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+SUMIFS(Sales_Data!H:H,Sales_Data!B:B,$I$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$1))/ (countifs(Sales_Data!B:B,$G$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+(countifs(Sales_Data!B:B,$H$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+(countifs(Sales_Data!B:B,$I$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2))

 

There is an error here. Is there a better way to do this? 

6 Replies

@WishIWerentAN00b 

How about

 

=(SUMIFS(Sales_Data!H:H,Sales_Data!B:B,$G$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+SUMIFS(Sales_Data!H:H,Sales_Data!B:B,$H$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+SUMIFS(Sales_Data!H:H,Sales_Data!B:B,$I$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$1))/(COUNTIFS(Sales_Data!B:B,$G$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+COUNTIFS(Sales_Data!B:B,$H$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2)+COUNTIFS(Sales_Data!B:B,$I$210,Sales_Data!AA:AA,A222,Sales_Data!AI:AI,"TOP",Sales_Data!A:A,$B$2))

@Hans Vogelaar Thanks for responding. The only difference is that you have the COUNTIFS capitalised, correct? It unfortunately doesnt make any difference whatsoever. The same error message is displayed.

@WishIWerentAN00b 

What is the error message? If I enter the formula in a workbook that has a sheet Sales_Data, it is accepted (but it returns #DIV/0! since the Sales_Data sheet is empty).

@Hans Vogelaar it returns a #DIV/0!
but i double checked that all of my cells are linked properly and not empty...

@WishIWerentAN00b 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

Thanks @Hans Vogelaar I will approach you in a private message.