I'm new to these Excel Discussion Boards/Forums as of yesterday, so please
bear with me. I've looked at hundreds of topics and many hundreds of posts,
but haven't found anything that gives me a clue as to how to go about dealing
with this problem:

I have multiple, adjacent columns, in the middle of a sheet, where the
values returned in the cells in these columns are either True or False. I
would like to be able to count up, from the last cell in a (ea.) column
(e.g.- G30:G3) until I reach a value of True, and then return that count
(total number of False or 0 values) into a cell in the same column, several
cells below the last True or False value. I have no need to continue
counting in a given column after that. Each column will have different
locations for the T/F values. Some may show the first value as True, whereas
another may count up 30 or 40 cells with False values before coming to a
True. From what I've read to date, I believe this could be accomplished with
a looping-type formula, using an "x = x - 1" operation to go up one cell at a
time, rather than (having to resort to) a macro, but I'm not sure how to put
it together. Perhaps an If...Then or Do...While/Until situation? or a
Count(If)/Countif? I think I know just enough to be dangerous, but not
enough to figure out even how to start this formula, if in fact, it can be
done with a formula &/or functions. Again, I'm really trying to avoid using
a macro. Any help would be appreciated. Good help would be revered.
--
Sociopath

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0. How
about *empty* cells, any of those? Better yet, post a small example and tell
us what result you expect.


--
Biff
Microsoft Excel MVP


"SwearBear" wrote in message
...
I'm new to these Excel Discussion Boards/Forums as of yesterday, so please
bear with me. I've looked at hundreds of topics and many hundreds of
posts,
but haven't found anything that gives me a clue as to how to go about
dealing
with this problem:

I have multiple, adjacent columns, in the middle of a sheet, where the
values returned in the cells in these columns are either True or False. I
would like to be able to count up, from the last cell in a (ea.) column
(e.g.- G30:G3) until I reach a value of True, and then return that count
(total number of False or 0 values) into a cell in the same column,
several
cells below the last True or False value. I have no need to continue
counting in a given column after that. Each column will have different
locations for the T/F values. Some may show the first value as True,
whereas
another may count up 30 or 40 cells with False values before coming to a
True. From what I've read to date, I believe this could be accomplished
with
a looping-type formula, using an "x = x - 1" operation to go up one cell
at a
time, rather than (having to resort to) a macro, but I'm not sure how to
put
it together. Perhaps an If...Then or Do...While/Until situation? or a
Count(If)/Countif? I think I know just enough to be dangerous, but not
enough to figure out even how to start this formula, if in fact, it can be
done with a formula &/or functions. Again, I'm really trying to avoid
using
a macro. Any help would be appreciated. Good help would be revered.
--
Sociopath

They are "0" & "1" values returned from an "=IF(OR(****),1,0) formula. I
think that makes them numerical values that are/can be treated as logicals??
Actually, most of the cells contain zeros. There are no *empty* cells. The
cells/columns are already formated to center. This spreadsheet is setup to
add data in a newly inserted row everytime there is new data to add. I want
to be able to count from the bottom/inserted row upwards to the first
instance of a "1". I'm not sure how to "post a small example", unless that's
what I just did at the top?? I'm a total neophyte and may have to ask for
many clarifications...
--
Sociopath


"T. Valko" wrote:

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0. How
about *empty* cells, any of those? Better yet, post a small example and tell
us what result you expect.


--
Biff
Microsoft Excel MVP

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


"SwearBear" wrote in message
...
They are "0" & "1" values returned from an "=IF(OR(****),1,0) formula. I
think that makes them numerical values that are/can be treated as
logicals??
Actually, most of the cells contain zeros. There are no *empty* cells.
The
cells/columns are already formated to center. This spreadsheet is setup
to
add data in a newly inserted row everytime there is new data to add. I
want
to be able to count from the bottom/inserted row upwards to the first
instance of a "1". I'm not sure how to "post a small example", unless
that's
what I just did at the top?? I'm a total neophyte and may have to ask for
many clarifications...
--
Sociopath


"T. Valko" wrote:

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals
appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0.
How
about *empty* cells, any of those? Better yet, post a small example and
tell
us what result you expect.


--
Biff
Microsoft Excel MVP

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

I'll explain the formula later when I have more time. I tend to write
"deluxe" explanations and I'm a slow one finger typer so it takes me a long
time!

As for this stuff:

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'll need to see an example and the expected results.

--
Biff
Microsoft Excel MVP


"SwearBear" wrote in message
...
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

To additionally clarify: "=IF(OR(****),1,0) should have had a closing quote
on the end - I forgot it. The quotes are not in the formula itself, just in
my post. Also, the newly inserted data/row is added below the last/previous
data/row, not somewhere in the middle of the sheet. Hope that helps a little.
--
Sociopath


"T. Valko" wrote:

values returned in the cells in these columns are either True or False.
return that count (total number of False or 0 values)

Are these logical TRUE and FALSE or *text* True and False? Logicals appear
in uppercase and are centered in the cell. Also, are there zeros in some
cells? From the above it sounds like there are either True, False or 0. How
about *empty* cells, any of those? Better yet, post a small example and tell
us what result you expect.


--
Biff
Microsoft Excel MVP

hi, !

not sure i'm following you correctly -?-
assuming in your range [G3:30] are at lest two TRUE (real true values)
and you need to count (from bottom to up) how many "non-true" cells...

array-entered formulae (ctrl + shift + enter)

op1: =sum(large(if(g3:g30,row(g3:g30)),{1;2})*{1;-1})-1
op2: =large(if(g3:g30,row(g3:g30)),1)-large(if(g3:g30,row(g3:g30)),2)-1

sorry if i don't undestand your complete scenario

hth,
hector.

I'm new to these Excel Discussion Boards/Forums as of yesterday, so please
bear with me. I've looked at hundreds of topics and many hundreds of posts,
but haven't found anything that gives me a clue as to how to go about dealing
with this problem:

I have multiple, adjacent columns, in the middle of a sheet, where the
values returned in the cells in these columns are either True or False. I
would like to be able to count up, from the last cell in a (ea.) column
(e.g.- G30:G3) until I reach a value of True, and then return that count
(total number of False or 0 values) into a cell in the same column, several
cells below the last True or False value. I have no need to continue
counting in a given column after that. Each column will have different
locations for the T/F values. Some may show the first value as True, whereas
another may count up 30 or 40 cells with False values before coming to a
True. From what I've read to date, I believe this could be accomplished with
a looping-type formula, using an "x = x - 1" operation to go up one cell at a
time, rather than (having to resort to) a macro, but I'm not sure how to put
it together. Perhaps an If...Then or Do...While/Until situation? or a
Count(If)/Countif? I think I know just enough to be dangerous, but not
enough to figure out even how to start this formula, if in fact, it can be
done with a formula &/or functions. Again, I'm really trying to avoid using
a macro. Any help would be appreciated. Good help would be revered.
--
Sociopath

It may give you more of the information you're asking about if you look at
the post I just made to T. Valko. Pleaase let me know.
--
Sociopath


"Héctor Miguel" wrote:

hi, !

not sure i'm following you correctly -?-
assuming in your range [G3:30] are at lest two TRUE (real true values)
and you need to count (from bottom to up) how many "non-true" cells...

array-entered formulae (ctrl + shift + enter)

op1: =sum(large(if(g3:g30,row(g3:g30)),{1;2})*{1;-1})-1
op2: =large(if(g3:g30,row(g3:g30)),1)-large(if(g3:g30,row(g3:g30)),2)-1

sorry if i don't undestand your complete scenario

hth,
hector.

Héctor,

Your formulas return incorrect values for the problem. Please see the
previous post from T. Valko and my response to his post, as well as my
previous response to yours. I really appreciate your efforts and they have
given me some additional ideas to possibly help me with other problems, so
thanks very much for that.
--
Sociopath


"Héctor Miguel" wrote:

hi, !

not sure i'm following you correctly -?-
assuming in your range [G3:30] are at lest two TRUE (real true values)
and you need to count (from bottom to up) how many "non-true" cells...

array-entered formulae (ctrl + shift + enter)

op1: =sum(large(if(g3:g30,row(g3:g30)),{1;2})*{1;-1})-1
op2: =large(if(g3:g30,row(g3:g30)),1)-large(if(g3:g30,row(g3:g30)),2)-1

sorry if i don't undestand your complete scenario

hth,
hector.

hi, !

Your formulas return incorrect values for the problem.
Please see the previous post from T. Valko and my response to his post
as well as my previous response to yours.
I really appreciate your efforts and they have given me some additional ideas
to possibly help me with other problems, so thanks very much for that.

don't worry, "my" proposed formulae were made with assumptions on your data-type & arrangement
and i saw your response/s but, with Tony you are in the best hands (than mine)

if my "wild" ideas gives to you additional ideas (for other situations)... i'm glad for that
so, thanks to you (for your feed-back), i really appreciate it ;)

regards,
hector.