Here's my formula:
=SUMIFS('February 16 Data Table'!$AD:$AD,'February 16 Data Table'!$AI:$AI,"N",'February 16 Data Table'!$AJ:$AJ,"N",'February 16 Data Table'!$AK:$AK,"N",'February 16 Data Table'!$AG:$AG,"Y",'February 16 Data Table'!$AN:$AN,">=1/1/2016",'February 16 Data Table'!$AC:$AC,{"A","L","S","R"})I entered the formula as an array formula using CTRL + SHIFT + ENTER.
Currently, the SUMIFS only returns the sum of values where "A" is true for column AC. What do I need to do to fix the formula?
12 Answers
If one of your criteria arguments in a SUMIFS function is an array constant, the function will return an array of values which test true for each element. Accordingly, to sum that array, you need to wrap SUMIFS in a SUM function. So, assuming your formula is otherwise correct:
=SUM(SUMIFS('February 16 Data Table'!$AD:$AD,'February 16 Data Table'!$AI:$AI,"N",'February 16 Data Table'!$AJ:$AJ,"N",'February 16 Data Table'!$AK:$AK,"N",'February 16 Data Table'!$AG:$AG,"Y",'February 16 Data Table'!$AN:$AN,">=1/1/2016",'February 16 Data Table'!$AC:$AC,{"A","L","S","R"}))should work
2A SUMIFS formula does not take an array by itself. You can instead break it apart into =SUM and =IF statements. For example:
=SUM(IF('February 16 Data Table'!$AI:$AI,"N",IF('February 16 Data Table'!$AJ:$AJ,"N" .... ,'February 16 Data Table'!$AD:$AD)))))Replace ... with your other code. This is just a guideline.
2
