Forum Discussion
Averages of the past 3 months with SUMIFS & COUNTIFS
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
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))
- HansVogelaar 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.
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).
