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.
|