Forum Discussion

Copper Contributor

Aug 18, 2022

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,...

Formulas and Functions

HansVogelaar

MVP

Aug 18, 2022

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))

WishIWerentAN00b
Copper Contributor
Aug 18, 2022
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.
- HansVogelaar
  MVP
  Aug 18, 2022
  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).
  - WishIWerentAN00b
    Copper Contributor
    Aug 18, 2022
    HansVogelaar it returns a #DIV/0!
    but i double checked that all of my cells are linked properly and not empty...