SUMIF - not returning expected result

I have a table similar to below in columns A-D and Rows 1-4, but with many columns (this is simplified), I want the sum of all the data in columns with a column heading of x

I used this SUMIF formula: =SUMIF(A1:D1,"x",A2:D4) and expected the result to be 16 but got 3

Have I misunderstood the SUMIF function? What function should I use to get the result I was expecting?

x y z x
1 1 1 2
2 1 3 3
3 3 4 5

Hi,


Try sumproduct instead

=SUMPRODUCT((A2:D4)*(A1:D1="x"))

Thx Sweep that's great, but why doesn't SUMIF work, in my example it seems to only add the first row

To use SUMIF you have to do it like this (or variation thereof):

=SUMIF(A1:D1,"x",A2:D2) + SUMIF(A1:D1,"x",A3:D3) + SUMIF(A1:D1,"x",A4:D4)

This is because the dimensions of the criteria range (A1:D1) define the dimensions of the sum range. If the criteria range is 1 row deep, then the sum range will be internally recalculated within the function so it is only 1 row deep too. See section 3.2 here for a full explanation.



Of course, you could have a totals row in row 5, and then reference the totals row in your SUMIF formula. So in cell A5 put this:

=SUM(A2:A4)

and copy across. Then your SUMIF formula becomes

=SUMIF(A1:D1,"x",A5:D5)

Thanks Colin