Help with array in a SUMIFS formula

Occasional Visitor

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 AColumn B
Row 1a1
Row 2b2
Row 3c3
Row 4d4

 

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

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"}))

@leoacquarone 

Just to illustarte how array works

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

Result is the same