COUNTIF problem with NOW()

  • Thread starter Thread starter Spreadsheet
  • Start date Start date
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.
 
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
 
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
 
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
 
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
 
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.
 
Sorry, I put it together based on your previous posts. Franz's
solution should do it for you.

Regards

Steve
 
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]...
 
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 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
 
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
 
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
 
This should work for you:

=SUMPRODUCT((ALL!AC2:AC1000<>"")*(ALL!AC2:AC1000<(TODAY()-21))*(ALL!AG2:AG1000=""))
 
The formula you gave me returns a Zero (it shouldn't). Does anything
need to be changed?
 
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
 
This should work,

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



HTH

Stev
 
Back
Top