# COUNT help

#### redspanna

##### Well-known Member
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]

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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=""))

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

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?

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.

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

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

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=""))

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

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=""))

Replies
6
Views
206
Replies
0
Views
81
Replies
6
Views
436
Replies
1
Views
263
Replies
9
Views
443

1,221,212
Messages
6,158,556
Members
451,498
Latest member
tyshanklin1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back