how do I "SUMIF" multiple conditions are true?

[MrExcelMistress]

I'm trying to sum the values in a column if 2 different conditions (in 2 other columns) are true (so I'm evaluating 3 columns total).

For example, if I had a list of the prices of all the cars available at a dealership (each car listed on a different row), and I wanted to find the total cost of all toyota celicas listed, how would I do this?

Basically, I'm trying to create a formula that says "if the value in the make column = toyota AND the value in the model column = celica, then sum the related values in the price column."

I have a feeling it's an embedded formula, but i can't figure out how to do it! Please help!

 

[dcamos]

=sumproduct(--(Make Column = Cell with Make in it),--(Model Column = Cell with Model in it),(Price column))

 

[DonkeyOte]

Welcome to the Board.

=SUMPRODUCT(--(A1:A100="Toyota"),--(B1:B100="Celica"),C1:C100)

Assumes A1:A100 holds car makes, B1:B100 holds models and C1:C100 prices.

Notes: if using XL pre 07 your ranges can not be entire columns (A:A is a no-no), also each range must be of identical size (eg above 1:100)

HTH

 

[MrExcelMistress]

Thanks so much! I'll try this now and see what happens...

 

[MrExcelMistress]

Ok, I'm almost there, but I have one last little glich. I'm really trying to count one of the columns with several possible names, all starting with the same few letters.

Using the car example, I want to do the same analysis for all make = toyota and model "starts with c" (instead of exactly "celica"). I assumed this was as easy as the equation below, but it's not working. what gives?

=SUMPRODUCT(--(A1:A100="Toyota"),--(B1:B100="C*"),C1:C100)

 

[njimack]

=SUMPRODUCT(--(A1:A100="Toyota"),--(LEFT(B1:B100)="C"),(C1:C100))

 

[riaz]

Try with an autofilter. Filter your first column on car makes, and your second column on the model. This will hide all other makes and models. Then run a subtotal - example:=subtotal(c1:C100,109) which will subtotal all visible rows. For this to work, you need to be using Excel 2003 or above

To pick up all models beginning with C, make a custom autofilter criteria choosing "begins with C".

However, this will only give you a subtotal that will keep changing so you may have to note down the totals elsewhere. If you need to show the totals permanently at the bottom of your sheet for each variation, I would suggest a new column (D?) where you join up the names in columns A and B - example : =A1&" "&B1, which you copy down as required. You can then do a sumif on column D.

If you need a further subdivision, you could do another column (E) with the first letter of the model you need - example : =a1&" "&left(b1,1) and then do a sumif on that column.

The totals of the sumif do not need to be below columns D and E, so you could put all your variations on your subtotals under column C, then just hide columns D and E and those will not come out on your print.

Hope this helps.

 

[MrExcelMistress]

Using the equation below didn't work (with just the "C").
=SUMPRODUCT(--(A1:A100="Toyota"),--(LEFT(B1:B100)="C"),(C1:C100))

it works if i put one of the exact words, but not just the "C." Don't I need an "*" or something after the C to show that the C is just the first letter of the word?


Riaz - thanks for the response, and this would work, but i'm trying to automate it using a formula. the reason being - this is a weekly report i run, and i do this calculation for about 30 different items. if i can set up a formula, i just need to update the raw data each week. if i do the sort/filter option, i'd have to redo the sort/filter for each of the 30 items each week. not fun.

 

[njimack]

My bad, I was missing a bracket. Try:
=SUMPRODUCT(--(A1:A100="Toyota"),--(LEFT(B1:B100)="C")),(C1:C100))

You don't need an asterisk since the LEFT function will look at the first character of each cell in the range B1:B100

Using the equation below didn't work (with just the "C").
=SUMPRODUCT(--(A1:A100="Toyota"),--(LEFT(B1:B100)="C"),(C1:C100))

it works if i put one of the exact words, but not just the "C." Don't I need an "*" or something after the C to show that the C is just the first letter of the word?

 

[Scott Huish]

You don't need parenetheses around the C1:C100