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: [TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]=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.
[/TD]
[/TR]
</tbody>[/TABLE]

For example:
[TABLE="width: 576"]
<tbody>[TR]
[TD]SO
[/TD]
[TD]Support
[/TD]
[TD]Type of Meeting
[/TD]
[TD]Mechanic
[/TD]
[TD]Count
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]Reginald Smith^2
[/TD]
[TD]B. Lawford ^
[/TD]
[TD]F2F
[/TD]
[TD]R13
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Patricia Jones ^1
[/TD]
[TD]P. Redland
[/TD]
[TD]Tele.
[/TD]
[TD]U19
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Sidney Poitier ^2
[/TD]
[TD]P. Newton
[/TD]
[TD]F2F
[/TD]
[TD]R02
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Elizabeth Taylor ^2
[/TD]
[TD]E. Hernandez
[/TD]
[TD]Tele.
[/TD]
[TD]U19
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Paul Newman ^2
[/TD]
[TD]S. DeLorean
[/TD]
[TD]Tele.
[/TD]
[TD]R13
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Robert Redford
[/TD]
[TD]T. Manilow
[/TD]
[TD]F2F
[/TD]
[TD]U19
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Cecily Tyson ^2
[/TD]
[TD]J. Johnson
[/TD]
[TD]F2F
[/TD]
[TD]U19
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Robert Redford
[/TD]
[TD]W. Washington
[/TD]
[TD]Tele.
[/TD]
[TD]R02
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Paul Newman ^2
[/TD]
[TD]K. Kowalski
[/TD]
[TD]F2F
[/TD]
[TD]U19 ^
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Barry Jones ^3
[/TD]
[TD]S. DeLorean ^
[/TD]
[TD]Tele. ^
[/TD]
[TD]R13
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Aggregate Sum
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]Then, I have simple sums which both denote 12 as you can see. Thanks so much.
[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[montyfern]

Problem is: See Barry Jones has ^3? Three exponents? Countif's and Sumif's only count ONE instance of the exponent. I need a total of EVERY instance of ^ going ACROSS. So, the Barry Jones line should total FIVE not three. Barry has 3 ^, S. Delorean has one, Tele. has one. There's only FOUR columns: SO, Support, Type of Meeting & Mechanic. Does this make sense?

 

[thisoldman]

I assumed the first column only would have a caret followed by an integer; columns B through D can have a caret but no integer. I also assumed the integer could go above 9.

I hope this helps.

Book1
	A	B	C	D	E
1	SO	Support	Type of Meeting	Mechanic	Total
2	Reginald Smith^2	B. Lawford ^	F2F	R13	3
3	Patricia Jones ^10	P. Redland	Tele.	U19	10
4	Sidney Poitier ^2	P. Newton	F2F	R02	2
5	Elizabeth Taylor ^2	E. Hernandez	Tele.^	U19	3
6	Paul Newman ^2	S. DeLorean^	Tele.	R13^	4
7	Robert Redford	T. Manilow	F2F	U19	0
8	Barry Jones ^3	S. DeLorean ^	Tele. ^	R13	5
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIF(B2:D2, "*^*") + IFERROR(--RIGHT(A2, LEN(A2) - SEARCH("^", A2)), 0)

 

[montyfern]

Perfecto thisoldman! I've never used IFERROR & it's years since RIGHT, LEN & SEARCH. *Wow.* ^ Read my Mr. Excel book (Power Excel) and the question starts with: "Using Left, Mid, Right, Find, and Len make my head hurt." Haha I feel their pain but in this case your solution is using them & superb! UBER thanks!
--montyfern

 

[thisoldman]

I'm glad to help. Thanks for the complements.

 

[montyfern]

He added new criteria where the formula doesn't work: REVIEW MECHANISM LIST

Mechanism	Reviewable Unit #s
N01s (Contract Proposals)	3 pts for each Contract Proposal Example: 1 N01= 3 2 NO1s = 6
D43, F30 – F35, G11, K01, K02, K08, K11, K22 - K25, K30, K99, L30, L40, N43, P20, R00, R01, R03, R09, R13, R15, R18, R21, R21/R33, R24, R25, R33, R34, R37, R38, R38/K38, R41 – R44, R61/R33, UC1, UC4, UC6, UC7, U56, U44, U42, U24, U19, U01, T35, T32, SI2/R00, SC2, SC1,SB, RC1, UG3/UH3, UH2, UH2/UH3, UT2, X02, Y01, Y02	1 pt for each single-project application Example: 1 R01= 1 2 K23s, 1 K22, 1 R03 = 4
U19, P01, UM1, UM2, U54, SI2, P30, P50	5 pts for each multi-project applications Example: 1 P01= 5 2 U19s = 10 1 U19, 3 PO1s = 20

<tbody>
</tbody>

Now I have to add these exact points .. can you help again please?

 

[thisoldman]

What does the hyphen mean in "F30 - F35"? Perhaps it stands for F30, F31, F32, F33, F34, F35?
What is the significance of the slash, as in "R21/R33"?
What's to be done if the mechanism has a carat?

I think another small solved example, is needed.

 

[montyfern]

What does the hyphen mean in "F30 - F35"? Perhaps it stands for F30, F31, F32, F33, F34, F35?
What is the significance of the slash, as in "R21/R33"?
What's to be done if the mechanism has a carat?

I think another small solved example, is needed.

I'm thinking a macro with sumbycolor? Seems too complex to count various carats in various cells, some together. However,
1. Thanks for your quick answer. Yes, to F30 (F30, F31, F32, F33, F34, F35).
2. He means this: "There is only 7 to 10 multi-project mechanisms and they are worth 5 points each. The N01 is classified as a contract and not a grant and its worth 3. The rest are worth 1 point." The slash threw me off too.

What do you think?

 

[thisoldman]

How about a lookup table or two?

You have U19 in both the 1-point and the 3-point lists. I removed it from the 1-point list for testing. The 1-point lists extends from cell G2 to cell G67—I don't show the complete list below.

If a mechanism doesn't appear in either list, and is not 'N01', no points are counted.

Book1
	A	B	C	D	E	F	G	H
1	SO	Support	Meeting Type	Mechanism	Total		1 Point	3 Point
2	Reginald Smith^2	B. Lawford ^	F2F	R13	4		D43	P01
3	Patricia Jones ^10	P. Redland	Tele.	U19	13		F30	P30
4	Sidney Poitier ^2	P. Newton	F2F	R02	2		F31	P50
5	Elizabeth Taylor ^2	E. Hernandez	Tele.^	U19	6		F32	SI2
6	Paul Newman ^2	S. DeLorean^	Tele.	N01^	9		F33	U19
7	Robert Redford	T. Manilow	F2F	U19	3		F34	U54
8	Barry Jones ^3	S. DeLorean ^	Tele. ^	R13	6		F35	UM1
9							G11	UM2
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIF(B2:D2, "*^*") + IFERROR(--RIGHT(A2, LEN(A2) - SEARCH("^", A2)), 0) + 5 * (SUBSTITUTE(D2, "^", "") = "N01") + 3 * ISNUMBER(MATCH(SUBSTITUTE(D2, "^", ""), $H$2:$H$9, 0)) + ISNUMBER(MATCH(SUBSTITUTE(D2, "^", ""), $G$2:$G$67, 0))

 

[montyfern]

I must be doing something wrong, as the No1's are 5 points and the U19's are 3. I played around, transposing your 5's & 3's. When I add a ^ it adds a point. Can I send you the file to a DropBox site location? Thanks so much!