Help! with MECE tabulations

[Guest]

Hello - I need help fast.
I have a large spreadsheet that looks like this:
A2 = Customers
B2 = Oranges
C2 = Apples
D2 = Grapes
E2 = Bananas
F2 = Kiwi

I need a report that shows:
How many have Oranges only?
How many have Apples only?
How many have Grapes only?
etc.

I also need a report that ranks the customer by the most number of Kiwi's,
while still showing the count of the other fruits.

Please help!

 

[Guest]

Can I make an "IF" or "COUNT" statement that says:
I B2 > 0, but C2 and D2 and E2 and F2 = 0

Also, the raw data has some blanks, N?A, ? in the fields. Will this have to
be cleaned up first? I noticed on a "=SUM" it was not a problem.

I've tried pivot tables, but can't get mutually exclusive fields.
Thanks again.

 

[Guest]

first part of the question, in the columns next to your data to create a
Unique flag for each type. The below formula will be true if there are only
oranges, coping the formula in the columns to the right will create filters
for the other fruits. You can then apply AutoFilters to your sheet and get
the records that contain only Oranges (or any other fruit) by setting the
filter for the coresponding "filter" column True

=IF(B2=0,FALSE,IF(B2/SUM($B2:$F2)=1,TRUE,FALSE))

second part, rank by number of kiwi - For that one I would probably just
sort the list by number of kiwi in decending order and then create a counter
in an empty column to assign a rank.

felix