Count 1 & sum in number series

[skr971]

Hi All,

i need a formula for count 1 in number series like :

Column A
1121	Ans . 8
21
11
1431

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

i need a formula which is sum 1 in given number series

thanks in advannce

 

[skywriter]

Here's a non-array solution that seems to work. Confirmed with enter only.

100% credit to Michael M and Teeroy, this wouldn't even have entered my mind if they didn't come up with their solutions. I had no idea how to solve this problem. I was hoping I would learn something from someone else solving it and I did.

By the way the data references A1:A4 are just where I pasted the data I used to test this. Make sure the references reflect your actual data.

=SUMPRODUCT((LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,1,""))))

 

[Michael M]

Bruce
I think by using SUMPRODUCT you'll find the calculation is not as efficient.

 

[skywriter]

Bruce
I think by using SUMPRODUCT you'll find the calculation is not as efficient.

Probably so, I don't have that timing program to tell me, but I believe you.

I recently put up a post with an array formula and someone else came behind me and put up a non array formula and I asked what was wrong with my array formula and the answer was nothing, it's just that in some settings people forget to confirm with control shift enter and that can be a problem.

I know with your formula I get an answer that is wrong if I don't use control shift enter and the formula is to the side of and in the same row as any of the data.
So I just wondered if there was a non array way to do this and I decided to post it.

I didn't have a solution at all until you came up with yours, so thanks for the education, twice.

 

[Michael M]

No worries, don't get me wrong I wasn't having a shot at you....
You're correct about the CSE being left out, and there is nothing at all wrong with posting different options to get the same result.
Sometimes you see a couple of different ways of doing things.....look at them....and say..."I like that one"
even if it is a bit slower

Cheers and keep the posts coming...

 

[skywriter]

Cheers and keep the posts coming...

Ditto and ditto.

 

[My Aswer Is This]

Thanks for all the different ways to do things.