software

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 amp; 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) amp; 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

Hi!

If the dates are for *this year only* and start in January:

=SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;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

quot;angelaquot; gt; wrote in message
...
gt; I'm under deadline and need some help! My spreadsheet is:
gt; Column A = Date
gt; Column B = Month # (such as 1 for January)
gt; Column E = Expense $ Amount
gt; Column F = Revenue $ Amount
gt; Column G = Balance $ Amount
gt;
gt; I need a formula that total columns E amp; F driven by a YEAR TO DATE date
gt; (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell C6
gt; I
gt; have the current month # (1 for Jan) amp; to my calculation is : SUMIF
gt; (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me
gt; the
gt; result for that particular month number I have in cell C6. Is there a way
gt; I
gt; can get a YEAR TO DATE date in cell C6 to use in my formula?
gt;
gt; My goal is to let this formula calculate a year to date balance without
gt; having to change the formula parameters each month.
gt;
gt; I'll take any and all suggestions! Thank you!
gt; -
gt; angela
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:

gt; If the dates are for *this year only* and start in January:

Also, I just noticed a difference in ranges:

gt; =SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;C6,F14:F999)

F14 should be F10:

=SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;C6,F10:F999)

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; If the dates are for *this year only* and start in January:
gt;
gt; =SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;C6,F14:F999)
gt;
gt; If the dates may span multiple years then you'd need 2 cells to hold a
gt; start date and an end date and it would take a different formula. Post
gt; back if that's the case.
gt;
gt; Biff
gt;
gt; quot;angelaquot; gt; wrote in message
gt; ...
gt;gt; I'm under deadline and need some help! My spreadsheet is:
gt;gt; Column A = Date
gt;gt; Column B = Month # (such as 1 for January)
gt;gt; Column E = Expense $ Amount
gt;gt; Column F = Revenue $ Amount
gt;gt; Column G = Balance $ Amount
gt;gt;
gt;gt; I need a formula that total columns E amp; F driven by a YEAR TO DATE date
gt;gt; (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell
gt;gt; C6 I
gt;gt; have the current month # (1 for Jan) amp; to my calculation is : SUMIF
gt;gt; (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me
gt;gt; the
gt;gt; result for that particular month number I have in cell C6. Is there a
gt;gt; way I
gt;gt; can get a YEAR TO DATE date in cell C6 to use in my formula?
gt;gt;
gt;gt; My goal is to let this formula calculate a year to date balance without
gt;gt; having to change the formula parameters each month.
gt;gt;
gt;gt; I'll take any and all suggestions! Thank you!
gt;gt; -
gt;gt; angela
gt;
gt;
Biff - You are my hero!~ That worked perfectly. Thx!
--
angelaquot;Biffquot; wrote:

gt; P.S.
gt;
gt; C6 is the date cell. Enter whatever date you want and the formula will
gt; calculate based on any dates in column A that are less than or equal to the
gt; date entered in C6. That's why I noted this:
gt;
gt; gt; If the dates are for *this year only* and start in January:
gt;
gt; Also, I just noticed a difference in ranges:
gt;
gt; gt; =SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;C6,F14:F999)
gt;
gt; F14 should be F10:
gt;
gt; =SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;C6,F10:F999)
gt;
gt; Biff
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; gt; Hi!
gt; gt;
gt; gt; If the dates are for *this year only* and start in January:
gt; gt;
gt; gt; =SUMIF(A10:A999,quot;lt;=quot;amp;C6,E10:E999)-SUMIF(A10:A999,quot;lt;=quot;amp;C6,F14:F999)
gt; gt;
gt; gt; If the dates may span multiple years then you'd need 2 cells to hold a
gt; gt; start date and an end date and it would take a different formula. Post
gt; gt; back if that's the case.
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;angelaquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; I'm under deadline and need some help! My spreadsheet is:
gt; gt;gt; Column A = Date
gt; gt;gt; Column B = Month # (such as 1 for January)
gt; gt;gt; Column E = Expense $ Amount
gt; gt;gt; Column F = Revenue $ Amount
gt; gt;gt; Column G = Balance $ Amount
gt; gt;gt;
gt; gt;gt; I need a formula that total columns E amp; F driven by a YEAR TO DATE date
gt; gt;gt; (i.e. 4/30/06 to include all rows from January 1 to April 30). In cell
gt; gt;gt; C6 I
gt; gt;gt; have the current month # (1 for Jan) amp; to my calculation is : SUMIF
gt; gt;gt; (B10:B999,C6,E10:E999)-SUMIF(B10:B999,C6,F14:F999). This only gives me
gt; gt;gt; the
gt; gt;gt; result for that particular month number I have in cell C6. Is there a
gt; gt;gt; way I
gt; gt;gt; can get a YEAR TO DATE date in cell C6 to use in my formula?
gt; gt;gt;
gt; gt;gt; My goal is to let this formula calculate a year to date balance without
gt; gt;gt; having to change the formula parameters each month.
gt; gt;gt;
gt; gt;gt; I'll take any and all suggestions! Thank you!
gt; gt;gt; -
gt; gt;gt; angela
gt; gt;
gt; gt;
gt;
gt;
gt;

You're welcome. Thanks for the feedback!

Biff

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