|
How to count after 2 conditions are met
I need to count how many instances of 123 are found today (4/3)
I also need to count how many instances of 123 are found in the past week (probably just need to add ""&(TODAY()-7)) The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried dont allow arrays of the entire column. ------------------------------ Sample data A B 123 4/3/2006 13:45 456 4/3/2006 12:34 789 4/3/2006 11:23 123 4/3/2006 10:01 456 4/2/2006 11:11 456 4/2/2006 10:46 789 4/2/2006 10:23 ------------------------------ I tried using "="&(TODAY()) in many formulas, but the syntax is always wrong. |
How to count after 2 conditions are met
=SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY()))
-- Regards, Peo Sjoblom http://nwexcelsolutions.com "Dave Schwinger" wrote in message ... I need to count how many instances of 123 are found today (4/3) I also need to count how many instances of 123 are found in the past week (probably just need to add ""&(TODAY()-7)) The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried dont allow arrays of the entire column. ------------------------------ Sample data A B 123 4/3/2006 13:45 456 4/3/2006 12:34 789 4/3/2006 11:23 123 4/3/2006 10:01 456 4/2/2006 11:11 456 4/2/2006 10:46 789 4/2/2006 10:23 ------------------------------ I tried using "="&(TODAY()) in many formulas, but the syntax is always wrong. |
How to count after 2 conditions are met
I received a #VALUE! error with that exact formula. If I remove the INT and
change = to = it works though. Any ways around the array limitation of using absolute cell ranges? "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY())) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Dave Schwinger" wrote in message ... I need to count how many instances of 123 are found today (4/3) I also need to count how many instances of 123 are found in the past week (probably just need to add ""&(TODAY()-7)) The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried dont allow arrays of the entire column. ------------------------------ Sample data A B 123 4/3/2006 13:45 456 4/3/2006 12:34 789 4/3/2006 11:23 123 4/3/2006 10:01 456 4/2/2006 11:11 456 4/2/2006 10:46 789 4/2/2006 10:23 ------------------------------ I tried using "="&(TODAY()) in many formulas, but the syntax is always wrong. |
How to count after 2 conditions are met
It works because your date and times are text and not number, if they were
numbers you couldn't get a value error and it will be flawed since all text is greater than a number (TODAY()) is a number -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Dave Schwinger" wrote in message ... I received a #VALUE! error with that exact formula. If I remove the INT and change = to = it works though. Any ways around the array limitation of using absolute cell ranges? "Peo Sjoblom" wrote: =SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY())) -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Dave Schwinger" wrote in message ... I need to count how many instances of 123 are found today (4/3) I also need to count how many instances of 123 are found in the past week (probably just need to add ""&(TODAY()-7)) The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried dont allow arrays of the entire column. ------------------------------ Sample data A B 123 4/3/2006 13:45 456 4/3/2006 12:34 789 4/3/2006 11:23 123 4/3/2006 10:01 456 4/2/2006 11:11 456 4/2/2006 10:46 789 4/2/2006 10:23 ------------------------------ I tried using "="&(TODAY()) in many formulas, but the syntax is always wrong. |
| All times are GMT +1. The time now is 07:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com