my worksheet has names and dates when reviews and training is due. I need to
add how many people are up to date and want to add columns totals but can not
assign a value to the cells containing the due dates. Is there a way to
adding how many I have in each column, not counting the ones that are due
shortly? Many thanks

have you tried using the count or countif functions?

"Ani63" wrote:

my worksheet has names and dates when reviews and training is due. I need to
add how many people are up to date and want to add columns totals but can not
assign a value to the cells containing the due dates. Is there a way to
adding how many I have in each column, not counting the ones that are due
shortly? Many thanks

One way could be to conditionally count cells that have an entry. For example,
if a1:a9 contains some dates, the total due, excluding those due after say
05/10/2006, would be:

=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"05/10/2006")

For names, it would be possible to check if a cell has a value (entry) using
Isblank

HTH
Anthony

"Ani63" wrote:

my worksheet has names and dates when reviews and training is due. I need to
add how many people are up to date and want to add columns totals but can not
assign a value to the cells containing the due dates. Is there a way to
adding how many I have in each column, not counting the ones that are due
shortly? Many thanks

I was trying to answer the OP's query, but couldn't (and still can't) get it
to work dynamically. ie I can't get it to work with TODAY() instead of a
static date in the formula.

If I try:
=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"<TODAY()")
I get the total number of entries, not the number of entries before today.
Trying =COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"=B10") where B10 contains a date
doesn't work either.

Any ideas why this doesn't work?

--
Ian
--
"Anthony D" wrote in message
...
One way could be to conditionally count cells that have an entry. For
example,
if a1:a9 contains some dates, the total due, excluding those due after say
05/10/2006, would be:

=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"05/10/2006")

For names, it would be possible to check if a cell has a value (entry)
using
Isblank

HTH
Anthony

"Ani63" wrote:

my worksheet has names and dates when reviews and training is due. I need
to
add how many people are up to date and want to add columns totals but can
not
assign a value to the cells containing the due dates. Is there a way to
adding how many I have in each column, not counting the ones that are due
shortly? Many thanks

Hi,
To refer to another cell in the condition, the syntax is a concatenation
using &
e.g.
=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,""&B10) where B10 contains a date

(the first part could use counta instead for non-blank cells)

Anthony

"Ian" wrote:

I was trying to answer the OP's query, but couldn't (and still can't) get it
to work dynamically. ie I can't get it to work with TODAY() instead of a
static date in the formula.

If I try:
=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"<TODAY()")
I get the total number of entries, not the number of entries before today.
Trying =COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"=B10") where B10 contains a date
doesn't work either.

Any ideas why this doesn't work?

--
Ian
--
"Anthony D" wrote in message
...
One way could be to conditionally count cells that have an entry. For
example,
if a1:a9 contains some dates, the total due, excluding those due after say
05/10/2006, would be:

=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"05/10/2006")

For names, it would be possible to check if a cell has a value (entry)
using
Isblank

HTH
Anthony

"Ani63" wrote:

my worksheet has names and dates when reviews and training is due. I need
to
add how many people are up to date and want to add columns totals but can
not
assign a value to the cells containing the due dates. Is there a way to
adding how many I have in each column, not counting the ones that are due
shortly? Many thanks

Easy when you know how. Thanks.

I've also figured out that ""&TODAY() works as the criteria.

For the purposes of the OP, who wanted to ignore dates which were near to
the current date, adding or subtracting a number from the criteria (eg
""&TODAY()-7 ) would offset the cutoff date with reference to today,
making it a dynamic formula.

--
Ian
--
"Anthony D" wrote in message
...
Hi,
To refer to another cell in the condition, the syntax is a concatenation
using &
e.g.
=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,""&B10) where B10 contains a date

(the first part could use counta instead for non-blank cells)

Anthony

"Ian" wrote:

I was trying to answer the OP's query, but couldn't (and still can't) get
it
to work dynamically. ie I can't get it to work with TODAY() instead of a
static date in the formula.

If I try:
=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"<TODAY()")
I get the total number of entries, not the number of entries before
today.
Trying =COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"=B10") where B10 contains a
date
doesn't work either.

Any ideas why this doesn't work?

--
Ian
--
"Anthony D" wrote in message
...
One way could be to conditionally count cells that have an entry. For
example,
if a1:a9 contains some dates, the total due, excluding those due after
say
05/10/2006, would be:

=COUNTIF(A1:A9,"0")-COUNTIF(A1:A9,"05/10/2006")

For names, it would be possible to check if a cell has a value (entry)
using
Isblank

HTH
Anthony

"Ani63" wrote:

my worksheet has names and dates when reviews and training is due. I
need
to
add how many people are up to date and want to add columns totals but
can
not
assign a value to the cells containing the due dates. Is there a way
to
adding how many I have in each column, not counting the ones that are
due
shortly? Many thanks