SUM - IF - AND

C

Chuck D

Joined
Nov 23, 2007
Messages
28
Reaction score
2
Hi All,

I am trying to sum entries in a column if the data in previous columns matche different criteria..

Ex in column "B" items are either reb, blue, green or black...etc
in column "C" items are round, square, triangular ...etc
In column "H" have a quantity (Number) for each item listed.

I need to work out how many red squares there are

so: if I want to know how many red squares are in stock I want to SUM IF items listed as red in column "B" AND "Square" in column "C" from quantities in Column "H"

It must be some combination of SUM of quantities in column "H", only IF "B5: B100" =Red & AND "C5:C100= "Square"

I have been going round in circles for some time...Any help would be welcome..

Cheers,
Chuck D
 
If due to some reason you want to rely on SUMIF only (which you shouldn't) then you have to CONCATINATE or use a column to create a key...by using the formula =B1&"-"&C1...now use this new column as a basis to get the sum of quantities.
 
Thanks Becky, unfortunately forgot to mention I am still using Excel 2003... oops it does not like the "S" after SUMIF
So I will try Amjibhai's sugestion next..
Cheers.
 
Ah ok, I think SUMIFS first appeared in Excel 2010. Sorry about that! I hope the alternative solution works well for you :)
 
Chuck D said:
Thanks Becky, unfortunately forgot to mention I am still using Excel 2003... oops it does not like the "S" after SUMIF
So I will try Amjibhai's sugestion next..
Cheers.
Click to expand...
Thank yoy Amjibhai,

Your solution worked for me once I got my head around Syntax ( using numbers and text )
 
Back
Top