Mental Block - Excel Formula

[nycguy77]

need help regarding this small problem I am facing:


This is how my data looks like:


Car Sell Price

Audi 1

Audi

Audi

Audi 1

Buick

Buick

Buick 1

BMW 1

BMW 1

BMW 1


I am looking for a solution to find the count of cars sold by manufacturer.

So my ideal result should be


Audi-2

Buick-1

BMW-3


I am trying the array formula but it doesnt seem to give me the right answer

I use excel 2003. Any help is appreciated

 

[VaraK]

Hi nycguy,


Assuming your data of car name is in column A (A2:A11), and sell price is in column B (B2:B11), formula to achieve your result for Audi car is:

=SUMIF($A$2:$A$11,"Audi",$B$2:$B$11). Just change the car name in the formula to Buick or BMW instead of "Audi"

 

[nycguy77]

thanks for the reply.


What if my data looks like this instead:


Audi 10000.5

Audi

Audi

Audi 1500

Buick

Buick

Buick 16000

BMW 112

BMW 134

BMW 14556


I still want them as count. Your formula will give me a sum of the numbers. Any ideas?

 

[VaraK]

Then you can use this array formula :

=SUMPRODUCT(--($A$2:$A$11="Audi")*($B$2:$B$11<>"")) and confirm with ctrl+shift+enter

 

[webmax]

you this simple formula


=COUNTIF($A$2:$A$11,"*"&"Audi"&"*")

 

[shrivallabha]

VaraK,


Your formula works for me without CSE and it can be:


=SUMPRODUCT(--($A$2:$A$11="Audi"),--($B$2:$B$11<>""))

Or

=SUMPRODUCT(($A$2:$A$11="Audi")*($B$2:$B$11<>""))


We do not need double unaries [--] for coercion when we are doing math operation, multiplying in this case.


Webmax,


You are not taking column B into account.

 

[jeffreyweir]

And then of course there's the often overlooked pivot table, which excels at tasks such as counting things