=COUNTIF(ALL!AC2:AC1000,"<NOW()-21")

This is the formula I'm using. I'm trying to determine how many dates
in column AC are more than three weeks ago. It's returning a 0 even
though it shouldn't. Can anyone see why? THanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

=COUNTIF(ALL!AC2:AC1000,"<NOW()-21")

This is the formula I'm using. I'm trying to determine how many dates
in column AC are more than three weeks ago. It's returning a 0 even
though it shouldn't. Can anyone see why? THanks.


Hi,

try this:

=COUNTIF(A2:A49,"<"&TODAY()-21)

in this case you should prefer TODAY instead of NOW, because you don't need
the time, just th day.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

do it this way and use today instead of now
=COUNTIF($A$2:$A$7,"<"&TODAY()-21)

--
Don Guillett
SalesAid Software

"Spreadsheet"
wrote in message
...

=COUNTIF(ALL!AC2:AC1000,"<NOW()-21")

This is the formula I'm using. I'm trying to determine how many dates
in column AC are more than three weeks ago. It's returning a 0 even
though it shouldn't. Can anyone see why? THanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile:
http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

The NOW formula should not be in quotes.

=COUNTIF(ALL!AC2:AC1000,"<"&NOW()-21)

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Thanks for replying. I'm now using
=COUNTIF(ALL!AC2:AC1000,"<"&TODAY()-21)

However, I'm having another problem. What I need to do is this:

Of all of the dates more than three weeks ago in column AC, how many of
those have a blank cell in column AG?


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

Thanks for replying. I'm now using
=COUNTIF(ALL!AC2:AC1000,"<"&TODAY()-21)

However, I'm having another problem. What I need to do is this:

Of all of the dates more than three weeks ago in column AC, how many
of those have a blank cell in column AG?


You can use this one:

=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Franz,

I'm not sure why you suggested SUMPRODUCT. I'm only interested in a
count.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

Franz,

I'm not sure why you suggested SUMPRODUCT. I'm only interested in a
count.

Trust me. This *is* a count...


--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

The reason I wasn't sure that this was a count is because the formula
resulted in a number much larger than the correct answer. Do you have
any other suggestions? Thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Could you post a sample of your data and what the result should be?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Sorry, I put it together based on your previous posts. Franz's
solution should do it for you.

Regards

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=555028

try this
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=" "))
or
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(len(ALL!AG2:AG1000)1))
or
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(isnumber(ALL!AG2:AG1000)))

--
Don Guillett
SalesAid Software

"Spreadsheet"
wrote in message
...

The reason I wasn't sure that this was a count is because the formula
resulted in a number much larger than the correct answer. Do you have
any other suggestions? Thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile:
http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Ok, THanks Franz. Your solution does work. However, the reason why I was
getting a very large number is because I was using:
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

However, I only have data up until row 157. So I changed it to:
=SUMPRODUCT((ALL!AC2:AC157<(TODAY()-21))*(ALL!AG2:AG157=""))

How can the formula be modified so that it counts the number of blank
cells in AG that have the correct data in AC but doesn't add the number
of blank cells in AG that have no date in AC. In other words, I don't
want to have to manually change the 157 to 158 when I add a new row.

Thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Spreadsheet Wrote:
Ok, THanks Franz. Your solution does work. However, the reason why I was
getting a very large number is because I was using:
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

However, I only have data up until row 157. So I changed it to:
=SUMPRODUCT((ALL!AC2:AC157<(TODAY()-21))*(ALL!AG2:AG157=""))

How can the formula be modified so that it counts the number of blank
cells in AG that have the correct data in AC but doesn't add the number
of blank cells in AG that have no date in AC. In other words, I don't
want to have to manually change the 157 to 158 when I add a new row.

Thanks.

Does anyone have any ideas, thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Nel post
*Spreadsheet* ha scritto:

Ok, THanks Franz. Your solution does work. However, the reason why I
was getting a very large number is because I was using:
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

However, I only have data up until row 157. So I changed it to:
=SUMPRODUCT((ALL!AC2:AC157<(TODAY()-21))*(ALL!AG2:AG157=""))

How can the formula be modified so that it counts the number of blank
cells in AG that have the correct data in AC but doesn't add the
number of blank cells in AG that have no date in AC. In other words,
I don't want to have to manually change the 157 to 158 when I add a
new row.



I think this sould do the job:

=SUMPRODUCT((INDIRECT("ALL!AC2:AC"&COUNTA(AC2:AC)) <(TODAY()-21))*(INDIRECT("ALL!AG2:AG"&COUNTA(AG2:AG))=""))

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Nel post
*Franz Verga* ha scritto:

Nel post
*Spreadsheet* ha scritto:

Ok, THanks Franz. Your solution does work. However, the reason why I
was getting a very large number is because I was using:
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

However, I only have data up until row 157. So I changed it to:
=SUMPRODUCT((ALL!AC2:AC157<(TODAY()-21))*(ALL!AG2:AG157=""))

How can the formula be modified so that it counts the number of blank
cells in AG that have the correct data in AC but doesn't add the
number of blank cells in AG that have no date in AC. In other words,
I don't want to have to manually change the 157 to 158 when I add a
new row.



I think this sould do the job:

=SUMPRODUCT((INDIRECT("ALL!AC2:AC"&COUNTA(AC2:AC)) <(TODAY()-21))*(INDIRECT("ALL!AG2:AG"&COUNTA(AG2:AG))=""))


The formula should be correct in this way:

=SUMPRODUCT((INDIRECT("ALL!AC2:AC"&COUNTA(AC:AC))< (TODAY()-21))*(INDIRECT("ALL!AG2:AG"&COUNTA(AG:AG))=""))

Errors due to copy & past... :-)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

This should work for you:

=SUMPRODUCT((ALL!AC2:AC1000<"")*(ALL!AC2:AC1000<( TODAY()-21))*(ALL!AG2:AG1000=""))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Spreadsheet"
wrote in message
...

Spreadsheet Wrote:
Ok, THanks Franz. Your solution does work. However, the reason why I was
getting a very large number is because I was using:
=SUMPRODUCT((ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))

However, I only have data up until row 157. So I changed it to:
=SUMPRODUCT((ALL!AC2:AC157<(TODAY()-21))*(ALL!AG2:AG157=""))

How can the formula be modified so that it counts the number of blank
cells in AG that have the correct data in AC but doesn't add the number
of blank cells in AG that have no date in AC. In other words, I don't
want to have to manually change the 157 to 158 when I add a new row.

Thanks.

Does anyone have any ideas, thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile:
http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

The formula you gave me returns a Zero (it shouldn't). Does anything
need to be changed?


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Spreadsheet wrote:
The formula you gave me returns a Zero (it shouldn't). Does anything
need to be changed?

In my last post I told you to correct the formula: there was a mistake due
to copy & past, so the correct formula is:

=SUMPRODUCT((INDIRECT("ALL!AC2:AC"&COUNTA(AC:AC))< (TODAY()-21))*(INDIRECT("ALL!AG2:AG"&COUNTA(AG:AG))=""))

If the formula returne you azero, maybe your dates are formatted as text...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

This should work,

=SUMPRODUCT(ISNUMBER(ALL!AC2:AC1000)*(ALL!AC2:AC10 00<(TODAY()-21))*(ALL!AG2:AG1000=""))



HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=555028

thanks, its working fine now.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=555028

Glad you got it working.

Regards,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=555028