COUNT help

[redspanna]

Hi all,

In column M of my worksheet I have the number 1 in various cells. I need a formula to count the number of times in the whole of column M that the number 1 is shown without the number 1 shown in the cell above or below
..also a formula to count the number of times that the number 1 is shown as series of that number.

for example

1
1

1

1

1
1
1

1
1

1

in the series above the results would be

number 1 by its self = 3
number 1 in groups of 2 = 2
number 1 in groups of 3 = 1

hope this makes sense - and hope you can help

thanks [/img]

 

[barry houdini]

Assuming your 1s are in the range M2:M100 and the cells M1 and M101 are blank, try

=SUMPRODUCT(--(M1:M99=""),--(M2:M100=""),--(M3:M101=""))

=SUMPRODUCT(--(M1:M98=""),--(M2:M99=1),--(M3:M100=1),--(M4:M101=""))

and

=SUMPRODUCT(--(M1:M97=""),--(M2:M98=1),--(M3:M99=1),--(M4:M100=1),--(M5:M101=""))


adjust ranges as necessary

 

[redspanna]

Thanks for the help, but I can't seem to get the formula to work.
Can I send you a sample worksheet to show what I want??
thanks

 

[barry houdini]

I can PM you my email address but what doesn't work, what results do you get?

I was assuming that the cells that don't contain 1s are blank, is that right or do they contain something else?

 

[redspanna]

Hi

that's correct the cells that don't contain the number 1 are blank - but they do contain a formula - will this effect the result as the results I was getting with your formual were incorrect even after changing the ranges.
cheers for your help

 

[barry houdini]

What's the formula? If it returns "" that's OK. If it returns " " then change to ""

 

[redspanna]

The formula is

=IF(J1=100,"","1")

so in cell M1 I want a value of 1 shown if the value in cell J1 = 100 or to be left blank if not

 

[barry houdini]

OK, now I see the problem(s)

there was a typo in my first formula - should be

=SUMPRODUCT(--(M1:M99=""),--(M2:M100=1),--(M3:M101=""))

Also, by using quotes around the 1 you're returning a text 1 not a numeric 1, whilst my formula is looking for numeric 1s. EITHER Change your formula to

=IF(J1=100,"",1)

or put quotes around the 1s in my formula(s), e.g.

=SUMPRODUCT(--(M1:M99=""),--(M2:M100="1"),--(M3:M101=""))

 

[redspanna]

think we have got it, one other quick question - what would the formula be to check for 4 number ones to be counted together?
thanks

 

[barry houdini]

You should be able to see a pattern emerging......

=SUMPRODUCT(--(M1:M96=""),--(M2:M97=1),--(M3:M98=1),--(M4:M99=1),--(M5:M100=1),--(M6:M101=""))