Multimple Sum Conditions

[Miles]

I have been trying his for ages and cant seem to work it out.

I need a formula that sums on more than one condition. Sumif only
appears to include 1 criteria.

For example:

Book Year Price

Red 1993 10
Blue 1994 20
Red 1993 50
Red 1994 30

I need a formula which will tell me the total price of red books in
1993, answer 60.

is there a specific funcion for this or is there a way of combining
sumif functions?

thanks

 

[Frank Kabel]

Hi

SUMIF only accepts onde condition. You can use SUMPRODUC. for your
example
=SUMPRODUCT((A1:A99="Red")*(B1:B99=1993)*(C1:C99))

HTH
Frank

 

[Miles]

Thanks but when entered this produces a #value!, the criteria appear to
have entered themselves into the same array, am i doing something
wrong?

 

[Norman Harker]

Hi Miles!

Try:

=SUMPRODUCT(($A$1:$A$99="Red")*($B$1:$B$99=1993)*($C$1:$C$99))

But better would be to put your range colors and of years in a range
of cells and then:

=SUMPRODUCT(($A$1:$A$99=E1)*($B$1:$B$99=F1)*($C$1:$C$99))

You can then copy this down and get your totals for combinations in
column E and F.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Miles!

Seems to work OK for me. Check your parentheses, "" and * are all OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Stefan Hägglund [MSFT]]

Hi Miles!

You could also use the array formula below:
=SUM(IF(A3:A6="Red",IF(B3:B6=1993,(C3:C6)),0))

When you have entered the text (or copied the formula above), then you have
to hold Ctrl + Shift down, when pressing Return.
Then your formula looks like this in the formula field:
{=SUM(IF(A3:A6="Red",IF(B3:B6=1993,(C3:C6)),0))}

I hope this helps.

Stefan Hägglund
Microsoft

 

[Miles]

at last!

that is great thanks

 

[Mark Graesser]

Miles,
You better check the data in your column C. Some if might be text and not numbers.

The SUM function in excel will ignore text entries. Therfore in the array formula any text entries in column C are skipped.

However, Excel returns a #VALUE error when you try to perform a math function (-,+,*,/) on a text value. Since the SUMPRODUCT function is multiplying zeros and ones with column C it will not work if any text entries exist within the range in column C.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Miles > wrote: -----

at last!

that is great thanks.