SUM - IF - AND


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
 
Ad

Advertisements

Joined
Feb 21, 2018
Messages
216
Reaction score
86
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.
 
Joined
Nov 23, 2007
Messages
28
Reaction score
2
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.
 

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
Ah ok, I think SUMIFS first appeared in Excel 2010. Sorry about that! I hope the alternative solution works well for you :)
 
Ad

Advertisements

Joined
Nov 23, 2007
Messages
28
Reaction score
2
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.
Thank yoy Amjibhai,

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top