# 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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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

adjust ranges as necessary

#### 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.
cheers for your help

#### 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
0
Views
83
Replies
2
Views
160
Replies
5
Views
168
Replies
1
Views
149
Replies
3
Views
167

### Forum statistics

1,176,443
Messages
5,903,135
Members
435,010
Latest member
bench205 ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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