how do i assign a value to a cell containing text

A

Ani63

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
 
A

Anthony D

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
 
I

Ian

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?
 
A

Anthony D

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
 
I

Ian

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.
 
Top