Formula help - Index, Match, SumProduct/SumIFs? Which one?

[im2bz2p345]

Hi all,

I cannot figure out what combination of Index, Match, and SumProduct (or SumIFs) that I would use to in order to achieve this result. Or perhaps there is more efficient/better way?

BMD - 2024 Packages Cad GCP PB HB Jan to mid Sept.xlsx
	C	D			G	H	I	J	K
1	Month	Day			RecordINIs	PB Day of Week	PB Unique INIs	PB Day of Week_PB Unique INI	PB INI Count
2	January	Tuesday			EAP	Monday	ABF	Monday_ABF
3	January	Tuesday			WQF	Monday	AGY	Monday_AGY
4	January	Tuesday			CER	Monday	AIF	Monday_AIF
5	January	Tuesday			CMP	Monday	AII	Monday_AII
6	January	Tuesday			LPP	Monday	AIP	Monday_AIP
7	January	Tuesday			EPM, EPP	Monday	AIU	Monday_AIU
8	January	Tuesday			CER	Monday	ATM	Monday_ATM
9	January	Tuesday			CER, WQF	Monday	BIL	Monday_BIL
10	January	Tuesday			CER, CWP, WQF	Monday	BWQ	Monday_BWQ
PB

Cell Formulas
Range	Formula
J2:J10	J2	=PB!$H2&"_"&PB!$I2

*Note*: I have column E & F hidden because it has sensitive information.

My array is Columns $D:$G

In column K ("PB INI Count"), I need a formula that can look at the array and look up the "PB Day of Week" (using column H) and return a count of how many times the 3 letters called "PB Unique INIs"(column I) appear in Column G.

This formula gives me a total count of how many times the 3 letters appears from column I appear in column G: =SUMPRODUCT(--(ISNUMBER(FIND(I2,$G:$G))))

BUT I need it also to filter down by the day of week.

Hope this makes sense. Happy to post any clarifications needed.

~ im2bz2p345

 

[im2bz2p345]

Also forgot to mention: The values 3 letter string from column will need to be a wildcard lookup ("*"&I2&"*") as column G could have commas, spaces, etc before or after the 3 letter string.

 

[Eric W]

This should be all you need, although it's hard to tell for sure since the example you gave doesn't have any matches.

Book1
	C	D			G	H	I	J	K
1	Month	Day			RecordINIs	PB Day of Week	PB Unique INIs	PB Day of Week_PB Unique INI	PB INI Count
2	January	Tuesday			EAP	Monday	ABF	Monday_ABF	0
3	January	Tuesday			WQF	Monday	AGY	Monday_AGY	0
4	January	Tuesday			CER	Monday	AIF	Monday_AIF	0
5	January	Tuesday			CMP	Monday	AII	Monday_AII	0
6	January	Tuesday			LPP	Monday	AIP	Monday_AIP	0
7	January	Tuesday			EPM, EPP	Monday	AIU	Monday_AIU	0
8	January	Tuesday			CER	Monday	ATM	Monday_ATM	0
9	January	Tuesday			CER, WQF	Monday	BIL	Monday_BIL	0
10	January	Tuesday			CER, CWP, WQF	Monday	BWQ	Monday_BWQ	0
Sheet2

Cell Formulas
Range	Formula
J2:J10	J2	=H2&"_"&I2
K2:K10	K2	=COUNTIFS(G:G,I2,H:H,"*"&H2&"*")

 

[im2bz2p345]

Thank you so much for the quick response @Eric W - I knew there had to be an easier way. I was trying all sorts of things with SUMPRODUCT and SUMIFS.

This is slightly edited formula from your solution is what I ended up using:
=COUNTIFS($G:$G,"*"&I2&"*",$D:$D,H2)

Appears to be exactly what I need and working perfectly. Thanks again. Will mark your response as the solution.

~ im2bz2p345

 

[Eric W]

Happy to help.