An easy answer I'm sure, so why am I stuck?
 

When I use the following formula, I get the answer I want (totaling of all
cells which indicate an individual was age 21 or over at time of admission).
However, when I attempt to get the opposite outcome (total of all cells which
indicate an individual was under age 21 at time of admission), I end up with
an outcome that merely counts the number of cells between G4:G5000. I don't
get why it works one way, and merely reversing the "" sign won't work.
Clearly the first formula is only counting those 21 or over, while the second
formula seems to be looking at all blank cells (which is why the value I
obtain is 4996). HELP! Thanks, Dan

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$500021))-This formula works

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))-This formula doesn't work

An easy answer I'm sure, so why am I stuck?
 

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))
the second formula seems to be looking at all blank cells

An empty cell evaluates as 0 and 0 is <21 so those cells get counted. Try it
like this:

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<""),--('ASAP
Database'!$G$4:$G$5000<21))

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
When I use the following formula, I get the answer I want (totaling of all
cells which indicate an individual was age 21 or over at time of
admission).
However, when I attempt to get the opposite outcome (total of all cells
which
indicate an individual was under age 21 at time of admission), I end up
with
an outcome that merely counts the number of cells between G4:G5000. I
don't
get why it works one way, and merely reversing the "" sign won't work.
Clearly the first formula is only counting those 21 or over, while the
second
formula seems to be looking at all blank cells (which is why the value I
obtain is 4996). HELP! Thanks, Dan

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$500021))-This formula works

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))-This formula doesn't work

An easy answer I'm sure, so why am I stuck?
 

On Sep 21, 3:13*pm, Dan the Man
wrote:
When I use the following formula, I get the answer I want (totaling of all
cells which indicate an individual was age 21 or over at time of admission).
However, when I attempt to get the opposite outcome (total of all cells which
indicate an individual was under age 21 at time of admission), I end up with
an outcome that merely counts the number of cells between G4:G5000. I don't
get why it works one way, and merely reversing the "" sign won't work.
Clearly the first formula is only counting those 21 or over, while the second
formula seems to be looking at all blank cells (which is why the value I
obtain is 4996). HELP! *Thanks, Dan

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$500021))-This formula works

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))-This formula doesn't work

Try...

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21),--('ASAP Database'!$G
$4:$G$5000<""))

Ken Johnson

An easy answer I'm sure, so why am I stuck?
 

Thanks Ken and Biff................Your suggestions worked.....................
Dan

"T. Valko" wrote:

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))
the second formula seems to be looking at all blank cells

An empty cell evaluates as 0 and 0 is <21 so those cells get counted. Try it
like this:

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<""),--('ASAP
Database'!$G$4:$G$5000<21))

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
When I use the following formula, I get the answer I want (totaling of all
cells which indicate an individual was age 21 or over at time of
admission).
However, when I attempt to get the opposite outcome (total of all cells
which
indicate an individual was under age 21 at time of admission), I end up
with
an outcome that merely counts the number of cells between G4:G5000. I
don't
get why it works one way, and merely reversing the "" sign won't work.
Clearly the first formula is only counting those 21 or over, while the
second
formula seems to be looking at all blank cells (which is why the value I
obtain is 4996). HELP! Thanks, Dan

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$500021))-This formula works

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))-This formula doesn't work

An easy answer I'm sure, so why am I stuck?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
Thanks Ken and Biff................Your suggestions
worked.....................
Dan

"T. Valko" wrote:

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))
the second formula seems to be looking at all blank cells

An empty cell evaluates as 0 and 0 is <21 so those cells get counted. Try
it
like this:

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<""),--('ASAP
Database'!$G$4:$G$5000<21))

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
When I use the following formula, I get the answer I want (totaling of
all
cells which indicate an individual was age 21 or over at time of
admission).
However, when I attempt to get the opposite outcome (total of all cells
which
indicate an individual was under age 21 at time of admission), I end up
with
an outcome that merely counts the number of cells between G4:G5000. I
don't
get why it works one way, and merely reversing the "" sign won't work.
Clearly the first formula is only counting those 21 or over, while the
second
formula seems to be looking at all blank cells (which is why the value
I
obtain is 4996). HELP! Thanks, Dan

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$500021))-This formula works

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))-This formula doesn't
work

An easy answer I'm sure, so why am I stuck?
 

An even better solution:

=COUNTIF('ASAP Database'!$G$4:$G$5000,"<21")

COUNTIF will ignore the empty cells.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))
the second formula seems to be looking at all blank cells

An empty cell evaluates as 0 and 0 is <21 so those cells get counted. Try
it like this:

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<""),--('ASAP
Database'!$G$4:$G$5000<21))

--
Biff
Microsoft Excel MVP


"Dan the Man" wrote in message
...
When I use the following formula, I get the answer I want (totaling of
all
cells which indicate an individual was age 21 or over at time of
admission).
However, when I attempt to get the opposite outcome (total of all cells
which
indicate an individual was under age 21 at time of admission), I end up
with
an outcome that merely counts the number of cells between G4:G5000. I
don't
get why it works one way, and merely reversing the "" sign won't work.
Clearly the first formula is only counting those 21 or over, while the
second
formula seems to be looking at all blank cells (which is why the value I
obtain is 4996). HELP! Thanks, Dan

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$500021))-This formula works

=SUMPRODUCT(--('ASAP Database'!$G$4:$G$5000<21))-This formula doesn't
work