Unable to identify appropriate formula for very simple scenario

[CJoQ]

Hi there,

Would appreciate some assistance with the very simple scenario below:
NAME FRUIT
Bill Orange
Bob Apple
Jade Apple
Gillian Apple
Gillian Orange
Bob Orange
Bill Apple
Bill Orange


So, there's our data, please imagine that 'Name' and 'Fruit' are i
separate columns next to each other.

THE GOA
In one cell, I want to show how many times 'Orange' occurs next t
'Bill', as a number.
AND (in a separate cell)
I want to show how many times 'Apple' occurs next to 'Bill', as
number.

Hopefully this would also be replicated, so that we could do the sam
for Gillian, Wendy, etc.

It feels like a COUNTIF / VLOOKUP amalgam, but I am really strugglin
(poor Excel skills). I am sure this would be a walk in the park for man
of you!

Any help would be really appreciated.

Thanks,
Chri

 

[Claus Busch]

Hi Chris,

Am Fri, 19 Jul 2013 12:01:40 +0100 schrieb CJoQ:

NAME FRUIT
Bill Orange
Bob Apple
Jade Apple
Gillian Apple
Gillian Orange
Bob Orange
Bill Apple
Bill Orange

try:
=COUNTIFS(A1:A10,"Bill",B1:B10,"Orange")


Regards
Claus B.

 

[Ron Rosenfeld]

Hi there,

Would appreciate some assistance with the very simple scenario below:
NAME FRUIT
Bill Orange
Bob Apple
Jade Apple
Gillian Apple
Gillian Orange
Bob Orange
Bill Apple
Bill Orange


So, there's our data, please imagine that 'Name' and 'Fruit' are in
separate columns next to each other.

THE GOAL
In one cell, I want to show how many times 'Orange' occurs next to
'Bill', as a number.
AND (in a separate cell)
I want to show how many times 'Apple' occurs next to 'Bill', as a
number.

Hopefully this would also be replicated, so that we could do the same
for Gillian, Wendy, etc.

It feels like a COUNTIF / VLOOKUP amalgam, but I am really struggling
(poor Excel skills). I am sure this would be a walk in the park for many
of you!

Any help would be really appreciated.

Thanks,
Chris

You could use a Pivot Table

Insert/Pivot Table

Drag NAME to rows
Drag FRUIT to column labels
Drag FRUIT to Values

If it does not automatically come up with Count in the Values area, click on the dropdown arrow, select Value Field Settings, and select Count in the "Summarize Value field by" dialog.

Totals of the rows and columns may get generated by default. You can select/deselect this by right-clicking in the Pivot Table itself, select PivotTable Options and go to the Total & Filters tab.
Finally, there are a variety of formatting options.