Countif or Sumif or Match? Counting exponents that are labels but weird results

[montyfern]

Good Day All!

Close but no cigar? VBA? Pivots? I have a list with labels where I need to count every instance of ^ or exponents that's embedded in individual cells. It will constantly change. I tried the following formulas:

=COUNTIFS(A2:D2,"*^*") =SUMIF(A2:D2,"*^*",$E$2:$E$11) =MATCH("^",A2:D11,0)-MATCH("^",A2:D11,0)-3 which gives the dreaded N/A error.

<tbody>
</tbody>

For example:

SO	Support	Type of Meeting	Mechanic	Count	Total
Reginald Smith^2	B. Lawford ^	F2F	R13	2
Patricia Jones ^1	P. Redland	Tele.	U19	1	2
Sidney Poitier ^2	P. Newton	F2F	R02	1	2
Elizabeth Taylor ^2	E. Hernandez	Tele.	U19	1	2
Paul Newman ^2	S. DeLorean	Tele.	R13	1	2
Robert Redford	T. Manilow	F2F	U19
Cecily Tyson ^2	J. Johnson	F2F	U19	1	2
Robert Redford	W. Washington	Tele.	R02
Paul Newman ^2	K. Kowalski	F2F	U19 ^	2	2
Barry Jones ^3	S. DeLorean ^	Tele. ^	R13	3
Aggregate Sum				12	12
Then, I have simple sums which both denote 12 as you can see. Thanks so much.

<tbody>
</tbody>

 

[thisoldman]

You can post the file, after properly anonymizing the data, to a cloud service and post the share link here.

I sent you a private message and you can get back to me.

 

[thisoldman]

After some off-the forums discussion I found I had an error in the carat count formula.

Also, there can be either one entry or two comma separated entries for the mechanism. If two entries are present the points must be summed together. A further complication is that a mechanism code may appear in more than one points list.

To make the lookup lists dynamic, I used three single-column structured tables in columns L, M, and N. The three-point and the five-point table extend beyond what I show here.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	SRO	Support	Type of Meeting	Mechanism	1stMech	2ndMech	Caret Pts	MechPts1	MechPts2	SUM() Total Pts		1 Point	3 Points	5 Points
2	Alex Smith	D. Alighieri	F2F	B52	B52		0	1	0	1		A20	Y07	B17
3	Blair Johnson	J. Baldwin^	Tele.^	B17, Y07	B17	Y07	2	6	3	11		A26	P51	JU88
4	Chris Williams ^3	J. Robinson	F2F^	B17,B17	B17	B17	4	6	6	16		AR2		ME210
5	Drew Brown	H. Godwineson	Tele.	Y07, B17	Y07	B17	0	3	6	9		B17		P30
6	Emerson Jones	M. Curie	Tele. ^	JU88, Y07	JU88	Y07	1	5	3	9		B23		P38
7	Frankie Miller	H. de Soto ^	F2F	AR2	AR2		1	1	0	2		B52		P50
NotTable_NoCondFormtng

Cell Formulas
Range	Formula
E2	=TRIM(LEFT(SUBSTITUTE(D2, ",", REPT(" ", 20)), 10))
F2	=IF(ISNUMBER(SEARCH(",", D2)), TRIM(SUBSTITUTE(D2, E2 & ",", "")), "")
G2	=COUNTIF(A2:D2, "*^*") + ISNUMBER(--RIGHT(A2)) * IFERROR(--RIGHT(A2, LEN(A2) - SEARCH("^", A2)) - 1, 0)
H2	=ISNUMBER(MATCH(E2, x1Tbl[1 Point], 0)) + 3 * ISNUMBER(MATCH(E2, x3Tbl[3 Points], 0)) + 5 * ISNUMBER(MATCH(E2, x5Tbl[5 Points], 0))
I2	=ISNUMBER(MATCH(F2, x1Tbl[1 Point], 0)) + 3 * ISNUMBER(MATCH(F2, x3Tbl[3 Points], 0)) + 5 * ISNUMBER(MATCH(F2, x5Tbl[5 Points], 0))
J2	=SUM(G2:I2)