# 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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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

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

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
430
Replies
4
Views
792
Replies
0
Views
709
Replies
7
Views
554
Replies
4
Views
590

1,219,519
Messages
6,148,749
Members
450,833
Latest member
Andyboi

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

### Which adblocker are you using?

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