CountIf this... but also a date range?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lavachickie - 17 Feb 2007 01:00 GMT

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

Reply to this Message

Don Guillett - 17 Feb 2007 01:13 GMT

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

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> I'm a fairly light Excel user, which is why this is escaping me.
>
[quoted text clipped - 13 lines]
>
> Thanks. I've searched high and low and don't get it. =)

Reply to this Message

Dave Peterson - 17 Feb 2007 01:13 GMT

=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

> I'm a fairly light Excel user, which is why this is escaping me.
>
[quoted text clipped - 13 lines]
>
> Thanks. I've searched high and low and don't get it. =)

Signature

Dave Peterson

Reply to this Message

lavachickie - 17 Feb 2007 01:35 GMT

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

amy

Reply to this Message