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...
  - HansVogelaar
    MVP
    Aug 18, 2022
    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.