COUNTIF problem with NOW()

S

Spreadsheet

=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.
 
F

Franz Verga

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.
Click to expand...


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
 
S

SteveG

The NOW formula should not be in quotes.

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

HTH

Steve
 
S

Spreadsheet

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 o
those have a blank cell in column AG
 
F

Franz Verga

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?
Click to expand...


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
 
S

Spreadsheet

Franz,

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

Franz Verga

Nel post *Spreadsheet* ha scritto:
Franz,

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

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
 
S

Spreadsheet

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

SteveG

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

Regards

Steve
 
D

Don Guillett

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
(e-mail address removed)
"Spreadsheet" <[email protected]>
wrote in message
news:[email protected]...
 
S

Spreadsheet

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

Spreadsheet

Spreadsheet said:
Ok, THanks Franz. Your solution does work. However, the reason why I wa
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 blan
cells in AG that have the correct data in AC but doesn't add the numbe
of blank cells in AG that have no date in AC. In other words, I don'
want to have to manually change the 157 to 158 when I add a new row.

Thanks.
Click to expand...

Does anyone have any ideas, thanks
 
F

Franz Verga

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.
Click to expand...


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
 
F

Franz Verga

Nel post *Franz Verga* ha scritto:
Nel post *Spreadsheet* ha scritto:



I think this sould do the job:

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


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
 
R

Ragdyer

This should work for you:

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

Spreadsheet

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

Franz Verga

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

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
 
S

SteveG

This should work,

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



HTH

Stev
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Countif gone wrong 2
Finding the weighted average of text that have been given values 1
COUNTIFS AND CRITERIA 2
Help with Excel 2007 COUNTIFS! 0
Countifs 1
Formula assistance please . . 3
Countif function 3
COUNTIF Function Formula Question 7

Top