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.

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

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.

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.