Multiple colume matching

[e.cichlid]

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.

 

[Jonmo1]

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]

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]

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

 

[MrKowz]

That makes SO MUCH SENSE! Thanks for the answer.

 

[e.cichlid]

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

Thanks again

 

[e.cichlid]

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]

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]

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