sum item within certain date range
 

hello everyone,

Good day, my question:

I need to calculate the number of times an item appears within a date
range. Meaning, if column A is contains my dates ranging from 1/1/05 - 7/1/05
and column B contains various symbols (ie A, B, C etc.), I need to calculate
how many A's appear between 3/1/05 - 3/31/05.

I had tried some solutions given:

=SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A"))

or

=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(A1:A100)="A"))

but unfortunately it doesn't work...
anyone can help me with this? Thank you for your time for reading my post....

apart from the second one having a mistake in the range (which I assume is a
typo), they both work for me.

Are you sure that they are actually dates, not text? Try this which might
show it

=SUMPRODUCT((--(A1:A100)=DATE(2005,3,1))*(--(A1:A100)<=DATE(2005,3,31))*(UP
PER(B1:B100)="A"))

other than that post some data examples.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"kwong" wrote in message
...
hello everyone,

Good day, my question:

I need to calculate the number of times an item appears within a date
range. Meaning, if column A is contains my dates ranging from 1/1/05 -
7/1/05
and column B contains various symbols (ie A, B, C etc.), I need to
calculate
how many A's appear between 3/1/05 - 3/31/05.

I had tried some solutions given:

=SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A"))

or


=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(A1:A
100)="A"))

but unfortunately it doesn't work...
anyone can help me with this? Thank you for your time for reading my
post....

It's usually easier to assign cells to accept your variables.
Revisions can then be accomplished more easily without changing the formula
itself, and entering dates is simplified.

Start date in C1,
End date in C2,
Symbol to count in C3,

=SUMPRODUCT((A1:A100=C1)*(A1:A100<=C2)*(B1:B100=C 3))

I don't believe that you need the Upper() function, since nothing here is
case sensitive.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"kwong" wrote in message
...
hello everyone,

Good day, my question:

I need to calculate the number of times an item appears within a date
range. Meaning, if column A is contains my dates ranging from 1/1/05 -
7/1/05
and column B contains various symbols (ie A, B, C etc.), I need to calculate
how many A's appear between 3/1/05 - 3/31/05.

I had tried some solutions given:

=SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A"))

or

=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(A1:A
100)="A"))

but unfortunately it doesn't work...
anyone can help me with this? Thank you for your time for reading my
post....

kwong Wrote:
hello everyone,

Good day, my question:

I need to calculate the number of times an item appears within a date
range. Meaning, if column A is contains my dates ranging from 1/1/05 -
7/1/05
and column B contains various symbols (ie A, B, C etc.), I need to
calculate
how many A's appear between 3/1/05 - 3/31/05.

I had tried some solutions given:

=SUMPRODUCT((MONTH(A1:A100)=3)*(YEAR(A1:A100)=2005 )*(UPPER(B1:B100)="A"))

or

=SUMPRODUCT((A1:A100=DATE(2005,3,1))*(A1:A100<=DA TE(2005,3,31))*(UPPER(A1:A100)="A"))

but unfortunately it doesn't work...
anyone can help me with this? Thank you for your time for reading my
post....


Hi kwong

You could try a pivot table

Paul


--
Paul Sheppard
------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=395751