Mix IF and MID

[tinferns]

Hi,
Can I mix IF with MID command?

I am trying a formula which is not working the way it should. Can you please help me ?

Q16 = 1, 0, 3, 7, 2 (I will have numbers between 1-9 in random order) I want to count all numbers above 1 (I dont want SUM). In this case I want result to be 4. (By COUNTing 1, 3, 7, and 2)

I tried : =IF(MID(Q16,1,1)>1,1)+IF(MID(Q16,4,1)>1,1)+IF(MID(Q16,7,1)>1,1)+IF(MID(Q16,10,1)>1,1)+IF(MID(Q16,13,1)>1,1,0)

I am getting result 5. Wherein it is even counting for a Zero. I want to count only 1-9.

Can you help me

Thanks in advance.

Martin

 

[tinferns]

<DIR>Hi PGC,
I had a small error in my query...
earlier I had mentioned that they will be max 7 cases with numbers from 0-9. Actually the case is max 7 cases with numbers from 0 - 365. So we can have cases like below:

1, 0, 343, 8, 10

In case of the above the result should be 4.

Current formula is double calculating any number over 9.. And the result shown is 7 for the above case.

<DIR>=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""),"0",""))

Can this be tweaked to meet my new requirement ?
Please advise..

Sorry for the confusion.

Thanks in advance

Martin.
</DIR></DIR>

 

[pgc01]

Hi Martin

There may be easier formulas, try:

=SUM((LEN(SUBSTITUTE(","&A3," ",""))-LEN(SUBSTITUTE(SUBSTITUTE(","&A3," ",""),{",",",0"},"")))*{1,-0.5})

You could also write a vba snippet or use one from one add-in. Aladin already gave a solution following that approach.

 

[pgc01]

An easier version of the formula:

=LEN(SUBSTITUTE(", "&A1,", 0",""))-LEN(SUBSTITUTE(SUBSTITUTE(", "&A1,", 0",""),",",""))

The difference is that this formula assumes that between 2 numbers there's always exactly 1 comma and one space. The formula in the previous post allowed any number of spaces.

 

[tinferns]

Thanks PCG,

This formula works just fine.. thanks a million.. cheers.... Martin