Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.... |
#2
|
|||
|
|||
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.... |
#3
|
|||
|
|||
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.... |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function...random search? | Excel Worksheet Functions | |||
Help! Selecting data according to date range | Excel Discussion (Misc queries) | |||
If formula for date range | Excel Discussion (Misc queries) | |||
Change the low date range regional setting. | Excel Discussion (Misc queries) | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) |