Need a Formula to count hight and low digits.

[serge]

Hi everyone,

I need help to find out how can I count how many High digits and how many Low digits from a list of 5 numbers ?

The Low digits would be consider : 0,1,2,3,4.
The High digits would be consider : 5,6,7,8,9.

I have in A1 : 05
I have in B1 : 17
I have in C1 : 29
I have in D1 : 34
I have in E1 : 36

and in G1 would be the formula that would give me the count of the low digits and H1 the High digits.

For this example G1 = 6
H1 = 4

Thank you for helping. Serge.

 

[mvptomlinson]

Hi Serge, try:

=SUMPRODUCT(--(MID(A1&B1&C1&D1&E1,ROW(INDIRECT("1:10")),1)={"0","1","2","3","4"}))

For 5-9, change the last set of numbers to "5","6",etc..

This assumes you will always use five 2-digit numbers (including leading zeros). If you need to account for longer numbers, the formula would need a slight adjustment.

 

[serge]

Thank you mvptomlinson,

For your quick respond but it need a small correction if you can ?, the formula need to count the leading "0 ".

Thanks. Serge.

 

[mvptomlinson]

The formula does count leading zeros.

Let me guess, you formatted a cell so that it shows two numbers like 05, but it actually only contains the number 5? If so, don't do that in this case. Excel formulas will not see that as 05. Change the cell formats for those cells to Text and the formula will work when you type 05 into one of the cells.

If you can't/won't change the cell format to text, you would have to change the formula to convert each of the five values into text and then calculate based on that. For example:

=SUMPRODUCT(--(MID(TEXT(A1,"00")&TEXT(B1,"00")&TEXT(C1,"00")&TEXT(D1,"00")&TEXT(E1,"00"),ROW(INDIRECT("1:10")),1)={"0","1","2","3","4"}))

 

[serge]

It works perfect,

Thanks so much that is exactly what I needed.
you are terrific.
Thanks!!

 

[sanrv1f]

another way

Excel Workbook
	A	B	C	D	E	F	G	H
1	05	07	12	29	30		7	3
Sheet1

Excel 2003

Cell Formulas
Range	Formula
G1	=SUMPRODUCT(--(--MID(A1:E1,{1;2},1)<=4))
H1	=SUMPRODUCT(--(--MID(A1:E1,{1;2},1)>4))