Countif or Countifs or some other extraction formula?

[linacherfas]

Hi everyone,

I'm trying to pull data from one column in a table into another tab so that I can create a visualization for a dynamic dashboard. The column includes state and city abbreviations, sometimes several jurisdictions in the same cell separated by commas, and many instances of "N/A". See the screenshot of the pivot table that summarizes what's in the column. I'm trying to extract the data so that I have a count of each individual jurisdiction (state or city, doesn't matter) separately, without the commas as in the pivot table.

When I tried doing COUNTIF formulas for each jurisdiction abbreviation separately, it doesn't capture the ones in the lists - for example, it returns 0 for HI because HI only appears as part of "LA, HI, PA". When I tried COUNTIFS for "HI" and "HI," it returned 0. Not sure why. Then I tried this version with wildcards: =COUNTIF(Table1[[#All],[Non-Consultant P2P Jurisdiction]],"*"&"HI"&"*") but it incorrectly returns 2 for Hawaii, because it counts HI and also the PHI (which is Philadelphia). I've tried several versions of the wildcard formula, leaving out the wildcard on one or the other side of the jurisdiction name, but I end up with incorrect counts for some jurisdictions however I do it.

I also tried combining COUNTIF with ISNUMBER and SEARCH, but that got me no results.

Please help?!! Huge thank you in advance!

Best wishes,
-lina

 

[jtakw]

Hi,

Are you looking for something like this:

Book3.xlsx
	A	B	C	D
1			Count of	Result
2	N/A		N/A	1
3	NYC		NYC	3
4	CA		CA	2
5	DE		DE	1
6	RI		RI	1
7	SC, UT, WA, TN		SC	1
8	BC		UT	1
9	MD		WA	2
10	TX		TN	1
11	TBD		BC	1
12	LA, HI, PA		MD	1
13	IA		TX	2
14	AZ		TBD	1
15	NYC, AZ		LA	1
16	AZ, PA		HI	2
17	PHI		PA	2
18	WA, PHI, CA		IA	1
19	PHI, HI		AZ	3
20	TX, NYC		PHI	3
Sheet974

Cell Formulas
Range	Formula
D2:D20	D2	=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&C2&","," "&A$2:A$20&","))))

 

[linacherfas]

Thank you SO MUCH! I think it works!

 

[jtakw]

You're welcome.