software

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 quot;gt;quot;amp;(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 quot;gt;=quot;amp;(TODAY()) in many formulas, but the syntax is always wrong.

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

--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Dave Schwingerquot; gt; wrote in message
...
gt;I need to count how many instances of 123 are found today (4/3)
gt; I also need to count how many instances of 123 are found in the past week
gt; (probably just need to add quot;gt;quot;amp;(TODAY()-7))
gt; The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried dont
gt; allow arrays of the entire column.
gt;
gt; ------------------------------
gt; Sample data
gt;
gt; A B
gt; 123 4/3/2006 13:45
gt; 456 4/3/2006 12:34
gt; 789 4/3/2006 11:23
gt; 123 4/3/2006 10:01
gt; 456 4/2/2006 11:11
gt; 456 4/2/2006 10:46
gt; 789 4/2/2006 10:23
gt;
gt; ------------------------------
gt;
gt; I tried using quot;gt;=quot;amp;(TODAY()) in many formulas, but the syntax is always
gt; wrong.
I received a #VALUE! error with that exact formula. If I remove the INT and
change = to gt;= it works though. Any ways around the array limitation of using
absolute cell ranges?

quot;Peo Sjoblomquot; wrote:

gt; =SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY()))
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Dave Schwingerquot; gt; wrote in message
gt; ...
gt; gt;I need to count how many instances of 123 are found today (4/3)
gt; gt; I also need to count how many instances of 123 are found in the past week
gt; gt; (probably just need to add quot;gt;quot;amp;(TODAY()-7))
gt; gt; The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried dont
gt; gt; allow arrays of the entire column.
gt; gt;
gt; gt; ------------------------------
gt; gt; Sample data
gt; gt;
gt; gt; A B
gt; gt; 123 4/3/2006 13:45
gt; gt; 456 4/3/2006 12:34
gt; gt; 789 4/3/2006 11:23
gt; gt; 123 4/3/2006 10:01
gt; gt; 456 4/2/2006 11:11
gt; gt; 456 4/2/2006 10:46
gt; gt; 789 4/2/2006 10:23
gt; gt;
gt; gt; ------------------------------
gt; gt;
gt; gt; I tried using quot;gt;=quot;amp;(TODAY()) in many formulas, but the syntax is always
gt; gt; wrong.
gt;
gt;
gt;

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

nwexcelsolutions.comquot;Dave Schwingerquot; gt; wrote in message
...
gt;I received a #VALUE! error with that exact formula. If I remove the INT and
gt; change = to gt;= it works though. Any ways around the array limitation of
gt; using
gt; absolute cell ranges?
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; =SUMPRODUCT(--(A2:A20000=123),--(INT(B2:B20000)=TODAY()))
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt; quot;Dave Schwingerquot; gt; wrote in
gt;gt; message
gt;gt; ...
gt;gt; gt;I need to count how many instances of 123 are found today (4/3)
gt;gt; gt; I also need to count how many instances of 123 are found in the past
gt;gt; gt; week
gt;gt; gt; (probably just need to add quot;gt;quot;amp;(TODAY()-7))
gt;gt; gt; The # of rows may be 100, 1000, or 10000. 1/2 of the formulas I tried
gt;gt; gt; dont
gt;gt; gt; allow arrays of the entire column.
gt;gt; gt;
gt;gt; gt; ------------------------------
gt;gt; gt; Sample data
gt;gt; gt;
gt;gt; gt; A B
gt;gt; gt; 123 4/3/2006 13:45
gt;gt; gt; 456 4/3/2006 12:34
gt;gt; gt; 789 4/3/2006 11:23
gt;gt; gt; 123 4/3/2006 10:01
gt;gt; gt; 456 4/2/2006 11:11
gt;gt; gt; 456 4/2/2006 10:46
gt;gt; gt; 789 4/2/2006 10:23
gt;gt; gt;
gt;gt; gt; ------------------------------
gt;gt; gt;
gt;gt; gt; I tried using quot;gt;=quot;amp;(TODAY()) in many formulas, but the syntax is always
gt;gt; gt; wrong.
gt;gt;
gt;gt;
gt;gt;