sum item within certain date range

[kwong]

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

[Paul Sheppard]

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<=DATE(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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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<=DATE(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....

[RagDyeR]

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

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" <[email protected]> wrote in message
news:[email protected]...
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<=DATE(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....