SUMIF function - can it sum multiple columns

I have the following table:

A B C D
Cash 5 7 3
Cash 1 2 9
Cash 4 6 6

I've tried using the following formula, thinking it would give me a result of 43.

=SUMIF('Sheet1'!A:A,"=Cash",'Sheet 1'!$B:$D)

But it's only giving me the sum of B (in this example, 10). What am I doing wrong?

The size of the sum range with SUMIF is always the same size as the criteria range, even if you specify it differently, so your formula will only sum the values in column B.

You could use

=SUMPRODUCT(('Sheet1'!A1:A100="Cash")*'Sheet1'!$B1:$D100)

or

=SUM(IF('Sheet1'!A1:A100="Cash",'Sheet1'!$B1:$D100))

The latter is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER

Note: in both cases you need to specify a range rather than using the whole column (unless you're using Excel 2007)

The latter formula will still give you a result if you have text anywhere in the sum range, the former will give an error in that situation

I'm getting a #VALUE error with the sumproduct function

I get a zero value if i do the Sum(if()

Originally Posted by sarabella

I'm getting a #VALUE error with the sumproduct function

I get a zero value if i do the Sum(if()

As per my post above #VALUE! error indicates, probably, that you have text in the sum range (text includes "formula blanks") so you probably need to use SUM(IF.....

You need to confirm the formula with CTRL+SHIFT+ENTER. To do that select cell with formula, press F2 then hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar and you should get the correct result

Thanks! That was my problem.

Reading is fundamental.