Biff(?),

Thank you SO MUCH for your answers to my question. The formula you
posted worked PERFECTLY! Unfortunately, it is more complex (to me) than I
can easily follow. Is there any chance you could walk me through it so I can
more clearly understand it and just what each operation is doing? Either
way, I intend to review each of the operators as soon as I have the time so I
can see how they relate to my spreadsheet situation.
I did not try to use your array formulay for three reasons: 1 - I have a
header in Row 3, so there will always be something in R1, R2, &/or R3; 2 - I
have an empty row between the last row of data entered and the Totals row so
that when I insert a new row for the new data, I will maintain my formatting
and inclusion of the new data in the formula parameters. That means that I
will ALWAYS have an empty cell as the last cell in the data row. And; 3 - I
don't understand the operation of the array formula well enough to see
exactly what it is doing! (Care to walk me through that one, too?)
I have two other issues with these same columns: 1 - I'd like to be able
to use a formula to get the count of the minimum number of "0"s between the
"1"s, and; 2 - Conversely, I'd like to be able to use a formula to get the
count of the maximum number of "0"s between the "1"s. I can't figure out how
to put a formula(s) together to do this, either. Are you interested, or
should I post this/these as (a) separate topic(s)?
As I mentioned initially, I'm new to this whole process and ignorant of
some of the details. Although I have clicked on the "Yes" button, I don't
see any option to "rate a post" other than the question/link. Should I?
Also, how does a post get assigned an "Answer" icon (the check mark in the
little, green circle?
Again, thanks more than I can say for saving me so much time stumbling
around to try and figure out the right formula to use. I was pretty certain
I wouldn't have to use a macro. You've totally amazed me by posting the
correct formula for this situation so quickly! I couldn't find anything
similar in all the help files I've read, nor the hundreds of postings, and I
thought it was going to be a tough nut to crack. You've made it look easy,
but that's what the top people in any field always do. I'm impressed.
--
Sociopath


"T. Valko" wrote:

Try this:

=COUNTIF(G30:INDEX(G3:G30,LOOKUP(2,1/(G3:G30=1),ROW(G3:G30)-MIN(ROW(G3:G30))+1)),0)

Or, this slightly shorter array formula** .

If there will *never* be a 1 G1:G2 we can use some shortcuts.

=SUM(IF(G30:INDEX(G:G,MAX((G1:G30=1)*ROW(G1:G30))) =0,1))

Note that the array formula will count *empty* cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP