How to use the SUMIF() function when the array is horizontal


I have 2 horizontal arrays starting from the cell A1 as below -

3	4	5	6
2	3	4	56

Now I want to use the SUMIF() function to calculate the sum of the 2nd row when value of the1st row matches with a particular value. I used below -

=SUMIF(A2:D2, A1:D1, "="&5)

However with above formula, I get error as


Can you please help to find the right approach for above data. My arrays are always horizontal, not vertical.

Any pointer will be highly appreciated.

The first parameter for SUMIF() is the array of test values, second is the condition, third is the array of values to add up. This is different from the order of parameters for SUMIFS(), which it looks like you have used.

=SUMIFS(A2:D2 ; A1:D1 ; "="&5), and …

=SUMIF(A1:D1 ; "="&5 ; A2:D2) should both work nicely.

Note that if you need to match an exact value, you don’t need the operator. So …

=SUMIF(A1:D1 ; 5 ; A2:D2) should also work.