Help: Sum characters in a range

[DougStroud]

I would like to sum three unique characters in a range: D1-D70. The characters are *,+,-

Thanks....

Doug

 

[Rick Rothstein]

I would like to sum three unique characters in a range: D1-D70. The characters are *,+,-

What exactly do you mean by "sum three unique characters"... count them? Are each of these characters in a cell by themselves or are they mixed in with other text in the cell? If they are mixed in, can there be more than one of them mixed in the text?

 

[Yongle]

Asuming that D1:D70 contains formula:

Is this function available in your version of Excel? (only works with recent versions)
=FORMULATEXT(D3)
(where D3 contains any formula)

If #NAME error is returned, then you need another method
Otherwise ...

In E1 copied down 70 rows
=LEN(FORMULATEXT($D1))-LEN(SUBSTITUTE(FORMULATEXT($D1),"+",""))
In F1 copied down
=LEN(FORMULATEXT($D1))-LEN(SUBSTITUTE(FORMULATEXT($D1),"*",""))
In G1 copied down
=LEN(FORMULATEXT($D1))-LEN(SUBSTITUTE(FORMULATEXT($D1),"-",""))

sum with
=SUM(E1:E70) etc

 

[Yongle]

If D1:D70 contains text (ie not formula) then use these:

=LEN($D1)-LEN(SUBSTITUTE($D1,"+",""))
=LEN($D1)-LEN(SUBSTITUTE($D1,"*",""))
=LEN($D1)-LEN(SUBSTITUTE($D1,"-",""))

 

[DougStroud]

Sorry for the delayed response- we were out of the office due to weather.
Yes, count them. Thank you for clarifying with proper term.

The characters sit alone in individual cells; example:
D1 "+"
D2 "*"
D3 "-"
D4 "+"

"+" occurs 2 times
"*" occurs 1 time
"-" occurs 1 time

 

[Scott T]

Book1
	C	D
1		+
2		*
3		-
4		+
5
6	+	2
7	*	1
8	-	1
Sheet1

Cell Formulas
Range	Formula
D6	=SUMPRODUCT(--($D$1:$D$4="+"))
D7	=SUMPRODUCT(--($D$1:$D$4="*"))
D8	=SUMPRODUCT(--($D$1:$D$4="-"))

 

[Yongle]

Try these
+
=COUNTIF(D1:D70,"*+*")
-
=COUNTIF(D1:D70,"*-*")
*
=COUNTA(D1:D70)-COUNTIF(D1:D70,"*+*")-COUNTIF(D1:D70,"*-*")

 

[DougStroud]

Thank you!

 

[DougStroud]

Thank you!

Try these
+
=COUNTIF(D1:D70,"*+*")
-
=COUNTIF(D1:D70,"*-*")
*
=COUNTA(D1:D70)-COUNTIF(D1:D70,"*+*")-COUNTIF(D1:D70,"*-*")

 

[DougStroud]

Thank you!

If D1:D70 contains text (ie not formula) then use these:

=LEN($D1)-LEN(SUBSTITUTE($D1,"+",""))
=LEN($D1)-LEN(SUBSTITUTE($D1,"*",""))
=LEN($D1)-LEN(SUBSTITUTE($D1,"-",""))