Need Assistance with a Count Function!

[alecp]

Hello there,

I need a working count function to determine the number of times that a certain column drops to zero. The tough part for me is that this column will change to 0 for a variable number of entries. Sometimes it will be at 0 for 5 entries, or just one - but either way I need to be able to count them the same.

Any help/advice will be much appreciated!

[NBVC]

So do you want to count how many groups of 0's there are whether it is one 0 or more 0's?

If so, try:

=SUM(IF(FREQUENCY(IF(B3:B53=0,ROW(B3:B53)),IF(B3:B53<>0,ROW(B3:B53)))>=1,1))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

If not, please tell us expected result and why?

[alecp]

So do you want to count how many groups of 0's there are whether it is one 0 or more 0's?

Yes that is what I want to do. However I tried to use the posted formula and it did not work for me (pardon my newbie level Excel expertise). Is there a way I could see the test excel file with the formula placed and functioning?

Thank you!

[NBVC]

Here you go.

After you enter the formula, you need to hold the CTRL and SHIFT keys down and hit ENTER so that { } brackets embrace the formula..

[alecp]

Ah that works wonderfully! Thank you...but I realized that I need to be able to have the column C in the test sheet have a single 1 whenever there are zeros, no matter the number of consecutive zeroes. This is so that in the full form of the file I have (that is always subject to 10,000 data entries changing constantly), I can see where the zeroes occured, but also use your formula that you have provided above to count them in total. Sorry for needing the additional help, but this would be wonderful if you could help me with it!

[NBVC]

You mean formula:

=IF(AND(B3=0,B2<>0),1,"")

copied down

[alecp]

Ah thats so simple, sometimes I just want to slap myself and my own stupidity. But than you for both points and I hope you have a great day! You're a good excel forum person moderator guy or however I should put that