Help with array in a SUMIFS formula

[leoacquarone]

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]

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

[#3]

@leoacquarone 

Just to illustarte how array works

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

Result is the same