|
COUNTIF problem with NOW()
=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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
COUNTIF problem with NOW()
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 |
| All times are GMT +1. The time now is 10:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com