COUNTIF problem

[IneedHel]

How do use the countif or another formula to count the first digit that is followed by text?

This is the formula I tried but it didn't work: =COUNTIF(C77:I77,"1-pri*")

What I need is to total up the first digit in a cell range .

Example: A1,A2,A3 = 8

cellA1 cellA2 cellA3 cellA4
1-pri5 5-pri1 2-pri3 8

 

[Simon4s]

how do use the countif or another formula to count the first digit that is followed by text?

This is the formula i tried but it didn't work: =countif(c77:i77,"1-pri*")

what i need is to total up the first digit in a cell range .

Example: A1,a2,a3 = 8

cella1 cella2 cella3 cella4
1-pri5 5-pri1 2-pri3 8

=sumproduct(--(left(a1:a14,1)="8"))

Left(A1,1) returns the first value.
if Left(A1,1)=8, excel will return "True"
-True= -1
--True = 1

 

[Zack Barresse]

Close. Leave out the condition to get the Sum.

=SUMPRODUCT(--LEFT(C77:I77,1))

This will sum the first character of every cell in the specified range.

 

[IneedHel]

Didn't work I get an error #VALUE !


I used this formula : =SUMPRODUCT(--LEFT(C77:I77,1))

 

[jtakw]

Hi,

Zack's formula should work, if you're getting #VALUE error, you may have leading spaces, try this, it will also SUM single or multiple leading digits (Don't forget to change cell references to suit your data):

Book1
	A
1	1-pri1
2	2-pri5
3	3-pri2
4	1-pri1
5	1-pri1
6
7	8
Sheet11

Cell Formulas
Range	Formula
A7	=SUMPRODUCT(--LEFT(A1:A5,FIND("-",A1:A5)-1))

 

[Zack Barresse]

Good point jtakw. If leading/trailing spaces is a problem, I'd recommend removing them, as opposed to using formula to work around it, and just solve the problem at the root of it.