COUNT help

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
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 :biggrin: [/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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 23, 2005
Messages
20,825
What's the formula? If it returns "" that's OK. If it returns " " then change to ""
 

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 27, 2005
Messages
1,493
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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=""))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top