 # Help with array in a SUMIFS formula

Hi everyone, it is my first post in this community.

I am trying to work with a very simple array formula as a criteria for a SUMIFS formula without success.

My data is as bellow

 Column A Column B Row 1 a 1 Row 2 b 2 Row 3 c 3 Row 4 d 4

Currently I am trying to SUM the values in column B filtered by the values in column A.

The formula I am trying to use is:

=SUMIFS(B1:B4;A1:A4;{"a","c"})

Excel says there is a problem with this formula and do not accept it.

My intention was to sum only the values in column B, row 1 and 3.

What I am doing wrong with the array?

2 Replies

# Re: Help with array in a SUMIFS formula

You may try SUMPRODUCT instead which can handle array calculations.

As per the data in your sample file...
=SUMPRODUCT((A1:A4={"a","c"})*B1:B4)

Or you will have to wrap your existing formula with SUM function like this...

=SUM(SUMIFS(B1:B4,A1:A4,{"a","c"}))

# Re: Help with array in a SUMIFS formula

Just to illustarte how array works

``=MMULT(TRANSPOSE((A1:A4="a")+(A1:A4="c")),B1:B4)``

Result is the same