Count Matches based of Criteria in other cells

bstokes2

New Member
Joined
Mar 22, 2020
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
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?
1APPLE
2corn
1ORANGE
2cheese
2APPLE
2ORANGE
1MIKE
1PEAR
2table
2PEAR
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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 NumberDateEvent Nameemail
11/1/1111First Eventname@GMAIL
11/1/1111First Eventlast@AOL
11/1/1111First Eventfirst@YAHOO
11/1/1111First Eventmiddle@HOTMAIL
11/1/1111First Eventintial@ATT
22/2/2222Second Eventmark@none
22/2/2222Second Eventname@GMAIL
22/2/2222Second Eventbrs@sdlkfjs
22/2/2222Second Eventfirst@YAHOO
33/3/3333Third Eventmiddle@HOTMAIL
33/3/3333Third Eventasdf@lkjasdf
33/3/3333Third Eventjohn@lkjasdf
33/3/3333Third Eventinitial@ATT
44/4/4444Fourth Eventkjdaf@ksad
44/4/4444Fourth Eventlast@AOL
44/4/4444Fourth Eventdfj@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
22
32
41
 
Upvote 0
That is a lot more complicated than your original post. I'll have to let someone else answer this one I'm afraid.
 
Upvote 0
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
ABCDEFG
1Event NumberDateEvent Nameemaileventmatches from 1
211/1/1111First Eventname@GMAIL22
311/1/1111First Eventlast@AOL31
411/1/1111First Eventfirst@YAHOO41
511/1/1111First Eventmiddle@HOTMAIL
611/1/1111First Eventintial@ATT
722/02/2222Second Eventmark@none
822/02/2222Second Eventname@GMAIL
922/02/2222Second Eventbrs@sdlkfjs
1022/02/2222Second Eventfirst@YAHOO
1133/03/3333Third Eventmiddle@HOTMAIL
1233/03/3333Third Eventasdf@lkjasdf
1333/03/3333Third Eventjohn@lkjasdf
1433/03/3333Third Eventinitial@ATT
1544/04/4444Fourth Eventkjdaf@ksad
1644/04/4444Fourth Eventlast@AOL
1744/04/4444Fourth Eventdfj@lkjdf
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=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))))
 
Upvote 0
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.
 
Upvote 0
Have any ideas on how to make this work without the filter function?
This looks simpler anyway. :)

bstokes2 2020-03-23 1.xlsm
ABCDEFG
1Event NumberDateEvent Nameemaileventmatches from 1
211/1/1111First Eventname@GMAIL22
311/1/1111First Eventlast@AOL32
411/1/1111First Eventfirst@YAHOO41
511/1/1111First Eventmiddle@HOTMAIL
611/1/1111First Eventinitial@ATT
722/02/2222Second Eventmark@none
822/02/2222Second Eventname@GMAIL
922/02/2222Second Eventbrs@sdlkfjs
1022/02/2222Second Eventfirst@YAHOO
1133/03/3333Third Eventmiddle@HOTMAIL
1233/03/3333Third Eventasdf@lkjasdf
1333/03/3333Third Eventjohn@lkjasdf
1433/03/3333Third Eventinitial@ATT
1544/04/4444Fourth Eventkjdaf@ksad
1644/04/4444Fourth Eventlast@AOL
1744/04/4444Fourth Eventdfj@lkjdf
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=SUMPRODUCT(--(A$2:A$17=F2),COUNTIFS(A$2:A$17,1,D$2:D$17,D$2:D$17))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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
Back
Top