SUM - IF - AND

[Chuck D]

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

 

[Becky]

What you need is SUMIFS - it allows you to specify multiple criteria.

https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

 

[AmjiBhai]

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.

 

[Chuck D]

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]

Ah ok, I think SUMIFS first appeared in Excel 2010. Sorry about that! I hope the alternative solution works well for you

 

[Chuck D]

Ah ok, I think SUMIFS first appeared in Excel 2010. Sorry about that! I hope the alternative solution works well for you

Amjibahi's solution works for me and therefore also for Excel 2003, Cheers Becky..

 

[Chuck D]

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 )

 

[AmjiBhai]

I feel immense delight.