If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum of named ranges conditional to date?
Hi all,
Say I have a spreadsheet listed as follows: A1 = 03/31/05 A2 = $5.00 B1 = 03/31/05 B2 = $10.00 C1 = 04/01/05 C2 = $20.00 The entire A column is named "Base_Date" and the entire B column is named "Base_Amount". I would like a formula that will sum values in the Amount column that correspond with a specific date range, ie. March only. This formula must reside in a different worksheet (within the same workbook). I've tried the following: =SUM(IF(Base_Date=DATEVALUE("03/01/2005"),IF(Base_Date=DATEVALUE("03/31/2005"),Base_Amount,0),0)) If I place the formula in a seperate worksheet from the data (as intended), its value is $35.00 (adds all values in column B) when I'm expecting it to be only $15.00. If I place the formula in the same worksheet as the data (not in columns A or B), its value is reported as 0. I'm sure I'm missing something miniscule here. Any suggestions? TIA, Sleazy |
#2
|
|||
|
|||
=SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount)
where X2 houses the first day date of the month/year of interset like: 03/01/2005 and Y2 one of: 03/31/2005 =DATE(YEAR(X2),MONTH(X2)+1,0) SleazyBreezy wrote: Hi all, Say I have a spreadsheet listed as follows: A1 = 03/31/05 A2 = $5.00 B1 = 03/31/05 B2 = $10.00 C1 = 04/01/05 C2 = $20.00 The entire A column is named "Base_Date" and the entire B column is named "Base_Amount". I would like a formula that will sum values in the Amount column that correspond with a specific date range, ie. March only. This formula must reside in a different worksheet (within the same workbook). I've tried the following: =SUM(IF(Base_Date=DATEVALUE("03/01/2005"),IF(Base_Date=DATEVALUE("03/31/2005"),Base_Amount,0),0)) If I place the formula in a seperate worksheet from the data (as intended), its value is $35.00 (adds all values in column B) when I'm expecting it to be only $15.00. If I place the formula in the same worksheet as the data (not in columns A or B), its value is reported as 0. I'm sure I'm missing something miniscule here. Any suggestions? TIA, Sleazy |
#3
|
|||
|
|||
Thank you very much! Thankfully, my date criteria is static, so the X2/Y2
variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) "Aladin Akyurek" wrote: =SUMIF(Base_Date,"="&X2,Base_Amount)-SUMIF(Base_Date,""&Y2,Base_Amount) where X2 houses the first day date of the month/year of interset like: 03/01/2005 and Y2 one of: 03/31/2005 =DATE(YEAR(X2),MONTH(X2)+1,0) |
#4
|
|||
|
|||
Okay, now I have a second, similar problem. Here's my new table:
A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
#5
|
|||
|
|||
D2: 3/1/05
E2: 3/31/05 F2: =MAX(IF(($A$1:$A$4=D2)*($A$1:$A$4=E2),$A$1:$A$4) ) which must be confirmed with control+shift+enter instead of just with enter. G2: =SUMIF($A$1:$A$4,F2,$B$1:$B$4) SleazyBreezy wrote: Okay, now I have a second, similar problem. Here's my new table: A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
#6
|
|||
|
|||
This really works well. Thanks so much!
It's humbling to know that I just can't grasp the concept of array formulas at this point of my Excel training. Back to the books! - Sleazy "Aladin Akyurek" wrote: D2: 3/1/05 E2: 3/31/05 F2: =MAX(IF(($A$1:$A$4=D2)*($A$1:$A$4=E2),$A$1:$A$4) ) which must be confirmed with control+shift+enter instead of just with enter. G2: =SUMIF($A$1:$A$4,F2,$B$1:$B$4) SleazyBreezy wrote: Okay, now I have a second, similar problem. Here's my new table: A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
#7
|
|||
|
|||
Sum of named ranges conditional to date?
I tried this formula and I can not seem to make it work. Is the MAX IF
formula supposed to generate a zero as a result? "Aladin Akyurek" wrote: D2: 3/1/05 E2: 3/31/05 F2: =MAX(IF(($A$1:$A$4=D2)*($A$1:$A$4=E2),$A$1:$A$4) ) which must be confirmed with control+shift+enter instead of just with enter. G2: =SUMIF($A$1:$A$4,F2,$B$1:$B$4) SleazyBreezy wrote: Okay, now I have a second, similar problem. Here's my new table: A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
#8
|
|||
|
|||
Sum of named ranges conditional to date?
Did you enter the formula using Control+Shift+Enter or just the Enter key by
itself? The formula requires the 3-key combination to work correctly. Rick "nukegirl" wrote in message ... I tried this formula and I can not seem to make it work. Is the MAX IF formula supposed to generate a zero as a result? "Aladin Akyurek" wrote: D2: 3/1/05 E2: 3/31/05 F2: =MAX(IF(($A$1:$A$4=D2)*($A$1:$A$4=E2),$A$1:$A$4) ) which must be confirmed with control+shift+enter instead of just with enter. G2: =SUMIF($A$1:$A$4,F2,$B$1:$B$4) SleazyBreezy wrote: Okay, now I have a second, similar problem. Here's my new table: A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
#9
|
|||
|
|||
Sum of named ranges conditional to date?
Thanks Rick! Got it to work. Appreciate the response.
"Rick Rothstein (MVP - VB)" wrote: Did you enter the formula using Control+Shift+Enter or just the Enter key by itself? The formula requires the 3-key combination to work correctly. Rick "nukegirl" wrote in message ... I tried this formula and I can not seem to make it work. Is the MAX IF formula supposed to generate a zero as a result? "Aladin Akyurek" wrote: D2: 3/1/05 E2: 3/31/05 F2: =MAX(IF(($A$1:$A$4=D2)*($A$1:$A$4=E2),$A$1:$A$4) ) which must be confirmed with control+shift+enter instead of just with enter. G2: =SUMIF($A$1:$A$4,F2,$B$1:$B$4) SleazyBreezy wrote: Okay, now I have a second, similar problem. Here's my new table: A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
#10
|
|||
|
|||
Sum of named ranges conditional to date?
Hi
I have a table where there are multiple entries for the same month and I want to produce monthly totals. I used the formula above (the MAX IF is working great) but the SUM IF is only returning one of the amounts per month rather than adding the different entries. Can anyone help? Thank you in advance! "Rick Rothstein (MVP - VB)" wrote: Did you enter the formula using Control+Shift+Enter or just the Enter key by itself? The formula requires the 3-key combination to work correctly. Rick "nukegirl" wrote in message ... I tried this formula and I can not seem to make it work. Is the MAX IF formula supposed to generate a zero as a result? "Aladin Akyurek" wrote: D2: 3/1/05 E2: 3/31/05 F2: =MAX(IF(($A$1:$A$4=D2)*($A$1:$A$4=E2),$A$1:$A$4) ) which must be confirmed with control+shift+enter instead of just with enter. G2: =SUMIF($A$1:$A$4,F2,$B$1:$B$4) SleazyBreezy wrote: Okay, now I have a second, similar problem. Here's my new table: A1: 03/01/2005 B1: $5.00 A2: 03/05/2005 B2: $12.00 A3: 04/05/2005 B3: $3.00 A4: 03/05/2005 B4: $50.00 As before, the A column is named Base_Date and the B column is named Base_Amount. I want to modify the SUMIF formula, as used below, so that it adds only the records associated with the *latest* date in a specified date range. Referencing the table above, if I specify 03/01/2005 and =03/31/2005, the formula should add B2+B4, and not include B1. However, if I then add the following to the table: A5: 03/29/2005 B5: $1.00 The formula should only report back B5 for its value, as A5 is the latest date in the allowed range. Thanks again in advance. :-) - Sleazy "SleazyBreezy" wrote: Thank you very much! Thankfully, my date criteria is static, so the X2/Y2 variables are not needed. I used your formula to achieve the desired result by moving the closing quotes as follows: =SUMIF(Base_Date,"=03/01/2005",Base_Amount)-SUMIF(Base_Date,"03/31/2005",Base_Amount) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Like 123, allow named ranges, and print named ranges | WP | General Discussion | 1 | April 8th, 2005 06:07 PM |
Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
How Do You Call Several Named Ranges From A Named Range | Minitman | Worksheet Functions | 14 | March 24th, 2004 11:18 PM |
Copying Worksheets with Named Ranges | Anthony Cravero | Worksheet Functions | 3 | December 19th, 2003 06:05 PM |
Counting cells with specific date ranges | Dino | Worksheet Functions | 1 | October 9th, 2003 08:00 PM |