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

[Dan the Man]

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$5000>21))-This formula works

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

 

[T. Valko]

=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))

 

[Ken Johnson]

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$5000>21))-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

 

[Dan the Man]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

An even better solution:

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

COUNTIF will ignore the empty cells.