Sumif & Dates

[Guest]

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

 

[Biff]

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

 

[Biff]

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

 

[Guest]

Biff - You are my hero!~ That worked perfectly. Thx!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff - You are my hero!~ That worked perfectly. Thx!