Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple yet complex scenario - goal seek problem | Excel Worksheet Functions | |||
simple yet complex scenario - goal seek problem | Excel Programming | |||
simple: Identify a cell that is out of number sequence in a list | New Users to Excel | |||
Unable To Create Simple Chart | Charts and Charting in Excel | |||
Scenario Analysis... simple | Excel Programming |