SUMIF function: sum if cell contain specific name

Question

I would like to run a function in Excel 2010 which has to:

• check which cell in a specific range contains a name
• if the previous task is true, sum the adjacent cell

Example:

In range B2:B227 there are some names, like Acura, Audi, BMW, Cadillac, etc. In range C2:C227 there are the number of cars those manufactures sold in 2012.

I want to calculate the sum of cars sold by certain manufacturers.

I was thinking about something similar, but it doesn't work. I think I made a mistake on the first step (recognize the names in cell B2:B227).

=SUMIF(B2:B227,OR("Audi","Acura","BMW","Cadillac"),D2:D227)

Thank you,

Gianluca

Answer 1

You can use SUMIF but as the result is an array (one sum for each criterion) you need to use SUM or SUMPRODUCT around the SUMIF to get the total, e.g.

=SUM(SUMIF(B2:B227,{"Audi","Acura","BMW","Cadillac"},D2:D227))

or list Audi, Acura etc in a range of cells e.g. K2:K5 and use this version

=SUMPRODUCT(SUMIF(B2:B227,K2:K5,D2:D227))