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" <(E-Mail Removed)> wrote in message
news:C6FE7D45-B94E-461F-ACD8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:C6FE7D45-B94E-461F-ACD8-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:57628F07-5167-46BF-9989-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:C6FE7D45-B94E-461F-ACD8-(E-Mail Removed)...
>> > 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
>>
>>
>>