Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help summing a range base on a date
A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the
data. I need a total by month, so I need to know the total for July, August, etc. Sumproduct works if the data is in 2 columns but not with the dates in a column and the data in a range. Your help is appreciated. A1 B1 C1 D1 Date Acct 1 Acct 2 Acct 3 7/1/09 $56.00 $34.00 $130 7/10/09 $57.00 $76.00 $230 7/13/09 $95.00 $33.00 $630 8/12/09 $78.00 $0.00 $270 8/16/09 $39.00 $79.00 $830 8/31/09 $87.00 $3.00 $278 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help summing a range base on a date
With the query date in cell E1 try the below
=SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy")),B2:B3800+C 2:C3800+D2:D3800) If this post helps click Yes --------------- Jacob Skaria "VinceW" wrote: A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the data. I need a total by month, so I need to know the total for July, August, etc. Sumproduct works if the data is in 2 columns but not with the dates in a column and the data in a range. Your help is appreciated. A1 B1 C1 D1 Date Acct 1 Acct 2 Acct 3 7/1/09 $56.00 $34.00 $130 7/10/09 $57.00 $76.00 $230 7/13/09 $95.00 $33.00 $630 8/12/09 $78.00 $0.00 $270 8/16/09 $39.00 $79.00 $830 8/31/09 $87.00 $3.00 $278 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help summing a range base on a date
Thanks for the help. That did work and I was able to modify it to work with
all of my data. Wish there was a around "B2:B3800+C2:C3800+D2:D3800". Was ok since I only had columns B to J to include, but if I would have had several hundred, what would have been the fix? Thanks again! "Jacob Skaria" wrote: With the query date in cell E1 try the below =SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy")),B2:B3800+C 2:C3800+D2:D3800) If this post helps click Yes --------------- Jacob Skaria "VinceW" wrote: A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the data. I need a total by month, so I need to know the total for July, August, etc. Sumproduct works if the data is in 2 columns but not with the dates in a column and the data in a range. Your help is appreciated. A1 B1 C1 D1 Date Acct 1 Acct 2 Acct 3 7/1/09 $56.00 $34.00 $130 7/10/09 $57.00 $76.00 $230 7/13/09 $95.00 $33.00 $630 8/12/09 $78.00 $0.00 $270 8/16/09 $39.00 $79.00 $830 8/31/09 $87.00 $3.00 $278 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help summing a range base on a date
You could try changing it to
=SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy"))*(B2:D3800) ) "VinceW" wrote: Thanks for the help. That did work and I was able to modify it to work with all of my data. Wish there was a around "B2:B3800+C2:C3800+D2:D3800". Was ok since I only had columns B to J to include, but if I would have had several hundred, what would have been the fix? Thanks again! "Jacob Skaria" wrote: With the query date in cell E1 try the below =SUMPRODUCT(--(TEXT(A2:A3800,"mmyy")=TEXT(E1,"mmyy")),B2:B3800+C 2:C3800+D2:D3800) If this post helps click Yes --------------- Jacob Skaria "VinceW" wrote: A partial list of my data is below. A2 - A3800 are dates, B2 - J3800 is the data. I need a total by month, so I need to know the total for July, August, etc. Sumproduct works if the data is in 2 columns but not with the dates in a column and the data in a range. Your help is appreciated. A1 B1 C1 D1 Date Acct 1 Acct 2 Acct 3 7/1/09 $56.00 $34.00 $130 7/10/09 $57.00 $76.00 $230 7/13/09 $95.00 $33.00 $630 8/12/09 $78.00 $0.00 $270 8/16/09 $39.00 $79.00 $830 8/31/09 $87.00 $3.00 $278 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with summing a range based on a date | Excel Worksheet Functions | |||
Adding Data from Date Range base on relative dates | Excel Worksheet Functions | |||
summing a date range | Excel Discussion (Misc queries) | |||
Change color of font base on time range | Excel Discussion (Misc queries) | |||
Counting cells base on a predetermined range | Excel Worksheet Functions |