0

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?

1
  • 1
    just wrap your SUMIFS inside a SUM function. Mar 16, 2016 at 1:01

2 Answers 2

0

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

2
  • That formula does NOT require CTRL+SHIFT+ENTER.
    – XOR LX
    Mar 16, 2016 at 9:04
  • @XORLX You are correct, of course. I was using something different for testing, and forgot to remove that from my answer. I will edit. Mar 16, 2016 at 10:40
-2

A 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
  • Please note that this formula as is will calculate over each row in the worksheet, that's 1mil operations for each condition.
    – Kyle
    Mar 15, 2016 at 17:41
  • A SUMIF or SUMIFS formula most certainly can take an array as a criteria argument. It will then return an array, which may need to be SUM'd depending on what you want to do with the results. Mar 16, 2016 at 0:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .