Count text in Column B based on criteria in Column A?

[resrzfl]

I have attempted to research this issue on Google but I can't seem to word the issue properly and therefore unable to find an answer - even in this forum.

THE SETUP: I have a list of places in Column A. Each place may appear multiple times depending on Column B. Column B contains text titles of events which occurred at each place. These titles are repetitive, sometimes multiple times for each place listed in Column A. If I have been unclear, here is a crude example:

Place Event
A 1
A 1
A 1
A 2
A 2
A 3
B 1
B 2
B 2
B 2
C 1
D 3
D 3

THE GOAL: I need to count how many times an event title may appear for each individual place in Column A. As seen above, "2" appears twice for "A", three times for "B", and not at all for "C" and "D".

Can anyone assist?

Thank you in advance.

 

[Andrew Poulsom]

Welcome to MrExcel.

Example:

=SUMPRODUCT(--(A2:A14="A"),--(B2:B14=1))

You can use cell references in place of "A" and 1.

 

[Jeff Meyers]

Here's an approach with an table summarizing the results:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Place	Event
2	A	1
3	A	1
4	A	1
5	A	2
6	A	2
7	A	3
8	B	1
9	B	2
10	B	2
11	B	2
12	C	1
13	D	3
14	D	3
15
16					Place
17				Event	A	B	C	D
18				1	3	1	1	-
19				2	2	3	-	-
20				3	1	-	-	2
21
22
23	---------	---------	---------	---------	---------	---------	---------	---------
24
Sheet2

Excel 2010

Cell Formulas
Range	Formula
E18	=SUMPRODUCT(--($A$2:$A$14=E$17),--($B$2:$B$14=$D18))
E19	=SUMPRODUCT(--($A$2:$A$14=E$17),--($B$2:$B$14=$D19))
E20	=SUMPRODUCT(--($A$2:$A$14=E$17),--($B$2:$B$14=$D20))
F18	=SUMPRODUCT(--($A$2:$A$14=F$17),--($B$2:$B$14=$D18))
F19	=SUMPRODUCT(--($A$2:$A$14=F$17),--($B$2:$B$14=$D19))
F20	=SUMPRODUCT(--($A$2:$A$14=F$17),--($B$2:$B$14=$D20))
G18	=SUMPRODUCT(--($A$2:$A$14=G$17),--($B$2:$B$14=$D18))
G19	=SUMPRODUCT(--($A$2:$A$14=G$17),--($B$2:$B$14=$D19))
G20	=SUMPRODUCT(--($A$2:$A$14=G$17),--($B$2:$B$14=$D20))
H18	=SUMPRODUCT(--($A$2:$A$14=H$17),--($B$2:$B$14=$D18))
H19	=SUMPRODUCT(--($A$2:$A$14=H$17),--($B$2:$B$14=$D19))
H20	=SUMPRODUCT(--($A$2:$A$14=H$17),--($B$2:$B$14=$D20))

 

[resrzfl]

Thanks for the reply, I will check it out.

I just realized there is one more component which has had me scratching my head.

There is a 3rd Column which contains each "Event"'s associated Date. I do not need to identify every date, however, I do need to match the number of times an Event has occurred for each location (which you have tackled) within a given year.

Any ideas?

Thank you again.

 

[Andrew Poulsom]

Just add another condition:

=SUMPRODUCT(--(A2:A14="A"),--(B2:B14=1),--(YEAR(C2:C14)=2012))

 

[resrzfl]

Thank you both!!