# Multiple colume matching

#### e.cichlid

##### New Member
Hello All,

Somewhat still a novice use to all of the great capabilities of Excel. Therefore, please excuse my lack of terminology.

On one sheet I have three columes.

Occasion Person Location
1 1 4
2 2 7
3 3 1
4 4 8
1 4 6

There are 4 occasions and 4 people and 1 to 9 different locations.

To save time in manipulating the data, how can I state how many time in occasion 1 did person 3 be in location 4? Is there a formula for this or do I need to creat a macro?

Thank you, I hope my explaination isn't too cryptic.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Jonmo1

##### MrExcel MVP
Welcome to the board...

Sumproduct is perfect for that..

=SUMPRODUCT(--(A1:A100="Occasion 1"),--(B1:B100="Person 3"),--(C1:C100="Location 4"))

IMPORTANT RULES
Ranges cannot be entire column refs like A:A, must specify row #s like A1:A100
Ranges must be same size
You can replace all the variables with cell references containing the same..Like

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1),--(C1:C100=F1))

D1 = Occasion 1
E1 = Person 3
F1 = Location 4

Hope that helps..

#### MrKowz

##### Well-known Member
Jonmo,

Just because I don't want to start a new thread on this... what does the "--" do exactly in a sumproduct?

Is it basically "if(this condition true)"

#### Jonmo1

##### MrExcel MVP
Is it basically "if(this condition true)"

No, the
A1:A100="Occasion 1"
that is the condtion, basically a TRUE/FALSE question, is A1 = "Occasion 1" ?

the -- converts the True or False answer to a number 1 or 0

True = 1
False = 0

Last edited:

#### MrKowz

##### Well-known Member

That makes SO MUCH SENSE! Thanks for the answer.

#### e.cichlid

##### New Member
Woah, thanks for the super quick reply.
I'll play witht the formula right away.

Thanks again

#### e.cichlid

##### New Member

Playing with the formula that you provided, I realize that I may not of explained myself correctly.

I have three columes. The first colum (the occasion) has numbers from 1 to 4 (each number occures multiple times). The second colum (person ID)has number like 101, 253, 302 and sp7m. The third colum (location) has numbers from 1 to 9.

Each row tells me on occasion 3 person 351 was in location 5.
Is there a formula to begin an analysis of for every occassion 2, person 203 was in location 9.

Much appreciated.

#### c_m

##### Well-known Member
try this
=SUMPRODUCT(INT(A1:A10=D1),INT(B1:B10=E1),INT(C1:C10=F1))
with your data array in first 10 rows.
its same as what jomno1 did

#### e.cichlid

##### New Member
AWESOME
Now I can say I put the effort in. I was confused by the SUM portion of the formula. Everything is working out now.

Thanks guys. Have a great weekend. I'll be working on the tons of data for the day

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,164,282
Messages
5,836,410
Members
430,425
Latest member
xlsee

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