Aug 18 2022 02:46 AM
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?
Aug 18 2022 03:08 AM
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))
Aug 18 2022 03:17 AM
Aug 18 2022 03:21 AM
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).
Aug 18 2022 03:24 AM
Aug 18 2022 03:28 AM
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.
Aug 18 2022 03:31 AM