# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  SUMIF function - can it sum multiple columns

## sarabella

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?

----------


## VBA Noob

Maybe





> =SUMPRODUCT((A1:A10="Cash")*(B1:D10))



Note Sumproduct won't work for whole column unless using 2007

VBA Noob

----------


## daddylonglegs

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

----------


## sarabella

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

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

----------


## daddylonglegs

> 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

----------


## sarabella

Thanks! That was my problem.

Reading is fundamental.

----------


## huyle2103

Good info, thanks. this is tutorial video How to use *Sum, Sumif, Sumifs* functions in Excel
https://www.youtube.com/watch?v=ZG510NTT3Bk

or you can use SUMPRODUCT instead of SUMIF, watch: Excel's Sumif and Sumproduct with multiple criteria
https://www.youtube.com/watch?v=6l5wpZrVvT0

----------

