sumproduct

[Guest]

Hi every body
I have this
Car CATEGORY SELL
Ford A 5
BMW A 3
Ford B 8
Toyota A 1
Ford C 4
Ford A 7
BMW A 6
Ford A 7
Toyota B 5
Ford A 9
I have two questions
1) I want to count the Ford and Toyota whose category is 'A'.
2) Also i want to sum the total sell of Ford and Toyota with category 'A'.
Please solve this .
Thanks & regards
Nad

 

[Guest]

=sumproduct(--(a1:a10="Ford"),--(b1:b10="A")) +
sumproduct(--(a1:a10="Toyota"),--(b1:b10="A")) '=== Count

=sumproduct(--(a1:a10="Ford"),--(b1:b10="A"),(c1:c10)) +
sumproduct(--(a1:a10="Toyota"),--(b1:b10="A"),(c1:c10)) '=== sell

 

[Guest]

thanks toppers
You are really topper.
i have another question
i want to count number of unique cars in range say A1:A10.
regards

 

[Dave Peterson]

one way:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

(adjust the range to match, but don't use the whole column)

 

[Guest]

Ok Dave
Its fine. I got the result but i could not understand the statement
(=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")).
will you please explain the statement.
if any other method to find.
Thanks & Regards
Nad

 

[Dave Peterson]

Try this in a new worksheet
Put a bunch of stuff in A1:A10 (duplicates ok--no empty cells)
Put this in B1:
=1/COUNTIF($A$1:$A$10,A1)
And drag down through b10
Now sum B1:B10

If a value occurs twice in the list, you'll see .5
If it occurs 3 times, you'll see .333333
If it only occurs once, you'll see 1.

This formula:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
essentially does this exact thing, but in one cell.

It also will fix any problems caused by empty cells. If you go back to your
test data and clear contents of one of the cells in A1:A10, you'll see #div/0
errors. (That's what the &"" will fix.)