How to count after 2 conditions are met

[Guest]

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

 

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY()))

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

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?

=SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY()))

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Peo Sjoblom]

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

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?