| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| This should be easy, but can't find an answer...requesting your in | Excel Worksheet Functions | |||
| Dynamic chart -easy question that has me stuck | Excel Discussion (Misc queries) | |||
| Stuck... | Excel Discussion (Misc queries) | |||
| i cant get the exact answer e.g answer is 13.49% i got 13.00% | Excel Discussion (Misc queries) | |||
| new user with easy question? not easy for me | New Users to Excel | |||
![Dan the Man[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
