Unable to identify appropriate formula for very simple scenario
 

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

Unable to identify appropriate formula for very simple scenario
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Unable to identify appropriate formula for very simple scenario
 

On Fri, 19 Jul 2013 12:01:40 +0100, CJoQ wrote:


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.