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

JohnDouglas

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

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

yours

john

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

phantom1975

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

Replies
8
Views
220
Replies
0
Views
336
Replies
7
Views
224
Replies
4
Views
217
Replies
10
Views
381

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,115
Messages
5,768,202
Members
425,459
Latest member
Danniey

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.

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

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