# 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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### barry houdini

##### MrExcel MVP
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=""))

#### redspanna

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

##### MrExcel MVP
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

##### Well-known Member

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.

#### barry houdini

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

#### redspanna

##### Well-known Member

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

##### MrExcel MVP
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

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

##### MrExcel MVP
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
1
Views
138
Replies
19
Views
191
Replies
3
Views
245
Replies
7
Views
339
Replies
8
Views
301

### Forum statistics

1,141,626
Messages
5,707,483
Members
421,510
Latest member
haroonstr ### 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