|
Sumif & Dates
I'm under deadline and need some help! My spreadsheet is:
Column A = Date Column B = Month # (such as 1 for January) Column E = Expense $ Amount Column F = Revenue $ Amount Column G = Balance $ Amount I need a formula that total columns E & F driven by a YEAR TO DATE date (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell C6 I have the current month # (1 for Jan) & to my calculation is : SUMIF (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me the result for that particular month number I have in cell C6. Is there a way I can get a YEAR TO DATE date in cell C6 to use in my formula? My goal is to let this formula calculate a year to date balance without having to change the formula parameters each month. I'll take any and all suggestions! Thank you! - angela |
Sumif & Dates
Hi!
If the dates are for *this year only* and start in January: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) If the dates may span multiple years then you'd need 2 cells to hold a start date and an end date and it would take a different formula. Post back if that's the case. Biff "angela" wrote in message ... I'm under deadline and need some help! My spreadsheet is: Column A = Date Column B = Month # (such as 1 for January) Column E = Expense $ Amount Column F = Revenue $ Amount Column G = Balance $ Amount I need a formula that total columns E & F driven by a YEAR TO DATE date (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell C6 I have the current month # (1 for Jan) & to my calculation is : SUMIF (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me the result for that particular month number I have in cell C6. Is there a way I can get a YEAR TO DATE date in cell C6 to use in my formula? My goal is to let this formula calculate a year to date balance without having to change the formula parameters each month. I'll take any and all suggestions! Thank you! - angela |
Sumif & Dates
P.S.
C6 is the date cell. Enter whatever date you want and the formula will calculate based on any dates in column A that are less than or equal to the date entered in C6. That's why I noted this: If the dates are for *this year only* and start in January: Also, I just noticed a difference in ranges: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) F14 should be F10: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F10:F999) Biff "Biff" wrote in message ... Hi! If the dates are for *this year only* and start in January: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) If the dates may span multiple years then you'd need 2 cells to hold a start date and an end date and it would take a different formula. Post back if that's the case. Biff "angela" wrote in message ... I'm under deadline and need some help! My spreadsheet is: Column A = Date Column B = Month # (such as 1 for January) Column E = Expense $ Amount Column F = Revenue $ Amount Column G = Balance $ Amount I need a formula that total columns E & F driven by a YEAR TO DATE date (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell C6 I have the current month # (1 for Jan) & to my calculation is : SUMIF (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me the result for that particular month number I have in cell C6. Is there a way I can get a YEAR TO DATE date in cell C6 to use in my formula? My goal is to let this formula calculate a year to date balance without having to change the formula parameters each month. I'll take any and all suggestions! Thank you! - angela |
Sumif & Dates
Biff - You are my hero!~ That worked perfectly. Thx!
-- angela "Biff" wrote: P.S. C6 is the date cell. Enter whatever date you want and the formula will calculate based on any dates in column A that are less than or equal to the date entered in C6. That's why I noted this: If the dates are for *this year only* and start in January: Also, I just noticed a difference in ranges: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) F14 should be F10: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F10:F999) Biff "Biff" wrote in message ... Hi! If the dates are for *this year only* and start in January: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) If the dates may span multiple years then you'd need 2 cells to hold a start date and an end date and it would take a different formula. Post back if that's the case. Biff "angela" wrote in message ... I'm under deadline and need some help! My spreadsheet is: Column A = Date Column B = Month # (such as 1 for January) Column E = Expense $ Amount Column F = Revenue $ Amount Column G = Balance $ Amount I need a formula that total columns E & F driven by a YEAR TO DATE date (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell C6 I have the current month # (1 for Jan) & to my calculation is : SUMIF (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me the result for that particular month number I have in cell C6. Is there a way I can get a YEAR TO DATE date in cell C6 to use in my formula? My goal is to let this formula calculate a year to date balance without having to change the formula parameters each month. I'll take any and all suggestions! Thank you! - angela |
Sumif & Dates
You're welcome. Thanks for the feedback!
Biff "angela" wrote in message ... Biff - You are my hero!~ That worked perfectly. Thx! -- angela "Biff" wrote: P.S. C6 is the date cell. Enter whatever date you want and the formula will calculate based on any dates in column A that are less than or equal to the date entered in C6. That's why I noted this: If the dates are for *this year only* and start in January: Also, I just noticed a difference in ranges: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) F14 should be F10: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F10:F999) Biff "Biff" wrote in message ... Hi! If the dates are for *this year only* and start in January: =SUMIF(A10:A999,"<="&C6,E10:E999)-SUMIF(A10:A999,"<="&C6,F14:F999) If the dates may span multiple years then you'd need 2 cells to hold a start date and an end date and it would take a different formula. Post back if that's the case. Biff "angela" wrote in message ... I'm under deadline and need some help! My spreadsheet is: Column A = Date Column B = Month # (such as 1 for January) Column E = Expense $ Amount Column F = Revenue $ Amount Column G = Balance $ Amount I need a formula that total columns E & F driven by a YEAR TO DATE date (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell C6 I have the current month # (1 for Jan) & to my calculation is : SUMIF (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me the result for that particular month number I have in cell C6. Is there a way I can get a YEAR TO DATE date in cell C6 to use in my formula? My goal is to let this formula calculate a year to date balance without having to change the formula parameters each month. I'll take any and all suggestions! Thank you! - angela |
| All times are GMT +1. The time now is 02:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com