Count Matches based of Criteria in other cells

[bstokes2]

I have a table set that I can't sort or adjust. I want to count the number of matches in group 2 based off of group 1. In this example it would be 3. Group 1 has 3 that match group 2, APPLE, ORANGE, PEAR.... MIKE doesn't match. What formula would do this?

1	APPLE
2	corn
1	ORANGE
2	cheese
2	APPLE
2	ORANGE
1	MIKE
1	PEAR
2	table
2	PEAR

 

[Trebor76]

Hi bstokes2,

Welcome to MrExcel!!

Assuming the table range is A2:B11 (change the following formula to suit) put this formula into Row 2 of an unused column...

=IF(A2=2,0,IF(AND(COUNTIFS($B$2:$B$11,B2,$A$2:$A$11,1)>0,COUNTIFS($B$2:$B$11,B2,$A$2:$A$11,2)>0),1,0))

...and copy it down to Row 11.

HTH

Robert

 

[bstokes2]

Hey Robert. Thank you for the quick response!

I might not have added enough contex though. I'll give the full picture. I have 4 columns that can't be moved or adjusted. The data sheet is large with 41 events displaying 100-300 emails each but here's a simplified version.

Event Number	Date	Event Name	email
1	1/1/1111	First Event	name@GMAIL
1	1/1/1111	First Event	last@AOL
1	1/1/1111	First Event	first@YAHOO
1	1/1/1111	First Event	middle@HOTMAIL
1	1/1/1111	First Event	intial@ATT
2	2/2/2222	Second Event	mark@none
2	2/2/2222	Second Event	name@GMAIL
2	2/2/2222	Second Event	brs@sdlkfjs
2	2/2/2222	Second Event	first@YAHOO
3	3/3/3333	Third Event	middle@HOTMAIL
3	3/3/3333	Third Event	asdf@lkjasdf
3	3/3/3333	Third Event	john@lkjasdf
3	3/3/3333	Third Event	initial@ATT
4	4/4/4444	Fourth Event	kjdaf@ksad
4	4/4/4444	Fourth Event	last@AOL
4	4/4/4444	Fourth Event	dfj@lkjdf

I'm trying to place a formula that will tell me how many emails from event 1, were in event 2, then 3, then 4 like below.

event#	total matches from event 1
2	2
3	2
4	1

 

[Trebor76]

That is a lot more complicated than your original post. I'll have to let someone else answer this one I'm afraid.

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming that your version of Excel 365 has the FILTER function try this, copied down.
It does not match your expected results, but I suspect it was an oversight that cells D6 and D14 are not identical.

bstokes2 2020-03-23 1.xlsm
	A	B	C	D	E	F	G
1	Event Number	Date	Event Name	email		event	matches from 1
2	1	1/1/1111	First Event	name@GMAIL		2	2
3	1	1/1/1111	First Event	last@AOL		3	1
4	1	1/1/1111	First Event	first@YAHOO		4	1
5	1	1/1/1111	First Event	middle@HOTMAIL
6	1	1/1/1111	First Event	intial@ATT
7	2	2/02/2222	Second Event	mark@none
8	2	2/02/2222	Second Event	name@GMAIL
9	2	2/02/2222	Second Event	brs@sdlkfjs
10	2	2/02/2222	Second Event	first@YAHOO
11	3	3/03/3333	Third Event	middle@HOTMAIL
12	3	3/03/3333	Third Event	asdf@lkjasdf
13	3	3/03/3333	Third Event	john@lkjasdf
14	3	3/03/3333	Third Event	initial@ATT
15	4	4/04/4444	Fourth Event	kjdaf@ksad
16	4	4/04/4444	Fourth Event	last@AOL
17	4	4/04/4444	Fourth Event	dfj@lkjdf
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=COUNT(FILTER(A$2:A$17,IF(A$2:A$17=F2,IF(COUNTIFS(A$2:A$17,1,D$2:D$17,D$2:D$17),A$2:A$17))))

 

[bstokes2]

Thanks Peter. But ah, I made a newbie mistake.

I have Excel 2019. Have any ideas on how to make this work without the filter function? If not, I just might have to upgrade to 365 for the sake of it.

 

[Peter_SSs]

Have any ideas on how to make this work without the filter function?

This looks simpler anyway.

bstokes2 2020-03-23 1.xlsm
	A	B	C	D	E	F	G
1	Event Number	Date	Event Name	email		event	matches from 1
2	1	1/1/1111	First Event	name@GMAIL		2	2
3	1	1/1/1111	First Event	last@AOL		3	2
4	1	1/1/1111	First Event	first@YAHOO		4	1
5	1	1/1/1111	First Event	middle@HOTMAIL
6	1	1/1/1111	First Event	initial@ATT
7	2	2/02/2222	Second Event	mark@none
8	2	2/02/2222	Second Event	name@GMAIL
9	2	2/02/2222	Second Event	brs@sdlkfjs
10	2	2/02/2222	Second Event	first@YAHOO
11	3	3/03/3333	Third Event	middle@HOTMAIL
12	3	3/03/3333	Third Event	asdf@lkjasdf
13	3	3/03/3333	Third Event	john@lkjasdf
14	3	3/03/3333	Third Event	initial@ATT
15	4	4/04/4444	Fourth Event	kjdaf@ksad
16	4	4/04/4444	Fourth Event	last@AOL
17	4	4/04/4444	Fourth Event	dfj@lkjdf
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=SUMPRODUCT(--(A$2:A$17=F2),COUNTIFS(A$2:A$17,1,D$2:D$17,D$2:D$17))