Multiple colume matching

e.cichlid

New Member
Joined
Jun 27, 2008
Messages
33
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

That makes SO MUCH SENSE! Thanks for the answer.
 

e.cichlid

New Member
Joined
Jun 27, 2008
Messages
33
Woah, thanks for the super quick reply.
I'll play witht the formula right away.

Thanks again
 

e.cichlid

New Member
Joined
Jun 27, 2008
Messages
33

ADVERTISEMENT

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
Joined
May 29, 2008
Messages
836
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
Joined
Jun 27, 2008
Messages
33
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 ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

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