Counting if matches 3 variables ......... end of my tether!

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
I've tried and tried :( . but don't seem to be able to get the right formula or even workout which function i should thorougly investigate.

please please please tell me how to sort this out.

I'm trying to produce a report that sums the total once three variables are met.

so, if i have three sites in:
column A: London, Paris, Tokyo.
column B: three different types of fruit (for simplicities sake :) ) apple, pear, banana.
column C: will say whether it has been "eaten" or "not eaten".

what i want to sum is each of the possible outcomes..

so for the three cities i'd like to see how many apples have been eaten and how many not eaten and the same for the pears and bananas.

please can anyone give me teh formula that will achieve this??

thanks very much in advance for your help

yours

john
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Re: Counting if matches 3 variables ......... end of my teth

VOILA!
Book1
ABCDEFGHIJ
1Londonappleeaten
2Londonpearnoteaten
3Tokyobanananoteaten
4Parisbanananoteaten
5Tokyopeareatenapplepearbanana
6Parisappleeateneatennoteateneatennoteateneatennoteaten
7ParisappleeatenLondon100400
8LondonpearnoteatenTokyo002004
9TokyobanananoteatenParis500001
10Parisappleeaten
11Tokyopeareaten
12Londonpearnoteaten
13Tokyobanananoteaten
14Parisappleeaten
15Parisappleeaten
16Londonpearnoteaten
17Tokyobanananoteaten
Sheet1
 

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
Select your whole table and create a pivot table of it

data->pivot table

Put column A, B & C on th eleft hand axis, then put c in the detail section in the middle. make sure it is set to count of (rather than sum of)

then you can collapse the detail under field 3 (eaten/not eaten) and you will have a table of all posibilities with the number
 

JohnDouglas

Board Regular
Joined
Jan 5, 2005
Messages
239
Re: Counting if matches 3 variables ......... end of my teth

phantom1975 - fantastic ... that works a treat. i really was going the wrong way with vlookup and match etc. thanks


cheria - i've tried out pivot tables in the past. but haven't ever been able to successfully insert a percentage of apples eaten. i looked and looked and looked but couldn't successfully find out how to insert this field. i also need to do long term trending which i hope will be easier if i avoid pivot tables.

thanks very much to both of you for looking and responding to this.

have a good day.
 

Forum statistics

Threads
1,147,691
Messages
5,742,661
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top