Formula - am I using the wrong one!

[Clare]

Hi all, my name is Clare and I'm trying to learn to use Excel formula in my job and have come up with a query I can't answer.

I have been using a Countif formula to count the number of cells containing a number i.e. =COUNTIF('EVAL SHEET'!$E$2:$E$162,1)+COUNTIF('EVAL SHEET'!$G$2:$G$162,1)+COUNTIF('EVAL SHEET'!$I$2:$I$162,1)+COUNTIF('EVAL SHEET'!$K$2:$K$162,1)+COUNTIF('EVAL SHEET'!$M$2:$M$162,1)

This has been working except when I have more than one number in a cell i.e. 1, 2, 3 . How can I change the formula to allow it to recognise the 1 in the cell with more numbers in? I had managed it with a different formula eg =SUMPRODUCT(LEN('EVAL SHEET'!$E$2:$E$164)-LEN(SUBSTITUTE('EVAL SHEET'!$E$2:$E$164,1,"")))+SUMPRODUCT(LEN('EVAL SHEET'!$G$2:$G$164)-LEN(SUBSTITUTE('EVAL SHEET'!$G$2:$G$164,1,"")))+SUMPRODUCT(LEN('EVAL SHEET'!$I$2:$I$164)-LEN(SUBSTITUTE('EVAL SHEET'!$I$2:$I$164,1,"")))+SUMPRODUCT(LEN('EVAL SHEET'!$K$2:$K$164)-LEN(SUBSTITUTE('EVAL SHEET'!$K$2:$K$164,1,"")))+SUMPRODUCT(LEN('EVAL SHEET'!$M$2:$M$164)-LEN(SUBSTITUTE('EVAL SHEET'!$M$2:$M$164,1,""))) but had the problem that it wouldn't distinguish between a 1 and an 11.

Any help would be very welcome. Thanks

 

[NARAYANK991]

Hi ,

Try this :

=SUM(COUNTIF('EVAL SHEET'!$E$2:$E$162,{"1","*1*"}))

Narayan

 

[Clare]

Hi Narayan, thank you for helping me. This unfortunately counts the 1 in 14. For example in one particular array I have one number 1 and three number 14's and it is giving me a total of 4. It definetly fixed the ability to count the 1 amongst multiple numbes in a cell. Do you have any idea how to stop it counting the 1 in 14?
thank you very much
clare

 

[NARAYANK991]

Hi Narayan, thank you for helping me. This unfortunately counts the 1 in 14. For example in one particular array I have one number 1 and three number 14's and it is giving me a total of 4. It definetly fixed the ability to count the 1 amongst multiple numbes in a cell. Do you have any idea how to stop it counting the 1 in 14?
thank you very much
clare

Hi ,

I tried it with 14 as a number ; the formula does not count it ; when I entered 14 as text ( prefixing it with a single apostrophe ) , the formula did count it.

Are some of your values entered as text ?

If so , can you confirm whether the only possibility of 1s which need to be counted besides lone 1s is when the 1 occurs in a string where it is followed by a comma , as in 1,2,3 ?

Narayan

 

[Clare]

Hi,

I have not entered them as text, I've just checked and made sure they are all numbers by going into the format cells option. I think in answer to second part that the answer should be yes if I'm understanding correctly. I have tried with and without spaces. I hope it is okay to upload this file to see if you can help me further - I've entered the formula you sent me into cell number H3. The ones in column 2 have different formulas as I've been experimenting!
thanking you again
clare