Problem counting data...not sure wich formula to use...

[BishopZ]

I am having a big problem with trying to count some data. I have two colums, one contains names and the other contains items. (I couldn't get the HTML add-in to work properly so you will have to bear with me)

Example:

..A..................B
John.............Apple
Jim..............Orange
John............Orange
Bill...............Apple
John.............Apple
Jim..............Orange

I need a formula that can tell me how many apples and oranges one person has:

..A......................B.........................C
.......................Apples................Oranges
Bill......................1.........................0
Jim.....................0.........................2
John...................2 .........................1


I have tried a few differnt ways with no luck... I just want it to look in column A for John's name and when it finds it I need it to count if there is an apple. They way I have my table set up is I only need each column to look for one thing, if it sees "apple" count 1 if not then 0... There would be another formula for the "oranges" to do the same thing.


Hope this makes sense and someone might have some suggestions.

Thanks

 

[Brian from Maui]

If I understood correctly (which is rare),

Book2
	A	B	C	D
1	Name	fruit
2	John	Apple
3	Jim	Orange
4	John	Orange
5	Bill	Apple
6	John	Apple
7	Jim	Orange
8
9		Apple	Orange
10	John	2	1
11	Jim	0	2
12	Bill	1	0
Sheet1

 

[PaddyD]

use a pivot table!

 

[Greg Truby]

Could you use a PivotTable?

Book5
	A	B	C	D
1	Person	Fruit
2	John	Apple
3	Jim	Orange
4	John	Orange
5	Bill	Apple
6	John	Apple
7	Jim	Orange
8	John	Grape
9	Jim	Orange
10	John	Orange
11	Bill	Banana
12	John	Apple
13	Jim	Orange
14	John	Apple
15	Jim	Orange
16	John	Orange
17	Bill	Banana
18	John	Banana
19	Jim	Orange
Sheet2

Book5
	A	B	C	D	E	F
3	CountofFruit	Fruit
4	Person	Apple	Banana	Grape	Orange	GrandTotal
5	Bill	1	2			3
6	Jim				6	6
7	John	4	1	1	3	9
8	GrandTotal	5	3	1	9	18
Sheet4

 

[MartinK]

Try e.g. SUMPRODUCT:

Book2
	A	B	C	D	E	F
1	A	B			Apples	Oranges
2	John	Apple		John	2	1
3	Jim	Orange		Jim	0	2
4	John	Orange		Bill	1	0
5	Bill	Apple
6	John	Apple
7	Jim	Orange
Sheet3

Martin

 

[Brian from Maui]

use a pivot table!

DUH!!!!

 

[BishopZ]

I have never gotten into Pivot tables before... But I will look into it now... at least I may learn something new