# How do I use an array as a condition in a SUMIFS formula?

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?

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

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

• That formula does NOT require CTRL+SHIFT+ENTER. 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

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.

• 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