I'm a fairly light Excel user, which is why this is escaping me.

I'm reporting on some data kept on one sheet. The first task was
simple: out of a column, count the occurences of a certain value,
based on a list of values already on the reporting page. That one's
simple:

=COUNTIF('Credit Cards'!S:S,Counts!A3)

But how do I do this if what I want is to ONLY count cells within a
certain date range? Can you have multiple arguements, or is there
another way to do this?

Let's say I wanted to get THAT number, but only for dates between
1/1/07 and 1/30/07?

Thanks. I've searched high and low and don't get it. =)

=sumproduct((a2:a22>b1)*(a2:a22<=b2))
to count

=sumproduct((a2:a22>b1)*(a2:a22<=b2)*b2:b22)
to sum
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"lavachickie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm a fairly light Excel user, which is why this is escaping me.
>
> I'm reporting on some data kept on one sheet. The first task was
> simple: out of a column, count the occurences of a certain value,
> based on a list of values already on the reporting page. That one's
> simple:
>
> =COUNTIF('Credit Cards'!S:S,Counts!A3)
>
> But how do I do this if what I want is to ONLY count cells within a
> certain date range? Can you have multiple arguements, or is there
> another way to do this?
>
> Let's say I wanted to get THAT number, but only for dates between
> 1/1/07 and 1/30/07?
>
> Thanks. I've searched high and low and don't get it. =)
>

=sumproduct(--(somerange>=date(2007,1,1),
--(somerange<=date(2007,1,30),
--('credit cards'!s1:s100=counts!a3))

if you really wanted just January (including the 31st) of 2007, you could use:

=sumproduct(--(text(somerange,"yyyymm")="200701",
--('credit cards'!s1:s100=counts!a3))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

lavachickie wrote:
>
> I'm a fairly light Excel user, which is why this is escaping me.
>
> I'm reporting on some data kept on one sheet. The first task was
> simple: out of a column, count the occurences of a certain value,
> based on a list of values already on the reporting page. That one's
> simple:
>
> =COUNTIF('Credit Cards'!S:S,Counts!A3)
>
> But how do I do this if what I want is to ONLY count cells within a
> certain date range? Can you have multiple arguements, or is there
> another way to do this?
>
> Let's say I wanted to get THAT number, but only for dates between
> 1/1/07 and 1/30/07?
>
> Thanks. I've searched high and low and don't get it. =)

--

Dave Peterson

Wow, thanks so much. I've not used SumProduct but it looks pretty
powerfull! THANKS!

amy