# Averages of the past 3 months with SUMIFS & COUNTIFS

Occasional Contributor

# 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

# Re: Averages of the past 3 months with SUMIFS & COUNTIFS

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

# Re: Averages of the past 3 months with SUMIFS & COUNTIFS

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

# Re: Averages of the past 3 months with SUMIFS & COUNTIFS

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

# Re: Averages of the past 3 months with SUMIFS & COUNTIFS

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

# Re: Averages of the past 3 months with SUMIFS & COUNTIFS

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.

# Re: Averages of the past 3 months with SUMIFS & COUNTIFS

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