YTD SUMIF based on current date

[garygoodguy]

Hi,

I have a spreadsheet set up with months and revenue across columns,
i.e:

July;August;September;Oct....Total, YTD Total
70000;65000;80000, 72000...Total, YTD Total

I also have a cell (I6) that updates and displays the current month when
logged in. Assuming the months start at A1 and data on A2 - how would I
go about creating a formula that only totals the YTD figure based on the
current date? I.e. if the current month is November, the sum total for
YTD only includes July, Aug, Sep, Oct, and Nov. (Please note the fiscal
year starts 01 July).

Thanks in advance. Please advise if you require any further details




--
garygoodguy

[GS]

garygoodguy presented the following explanation :

If I understand correctly, months are arrange left to right starting
with Jul and ending with Jun. In the YTD column, simply sum the other
columns. Those not yet used (Dec-Jun) will be empty and so not affect
the totals. I assume you are totalling the months at the bottom and so
the same formula will work there as well as individual rows.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

[garygoodguy]

'GS[_2_ Wrote:
> ;1608097']garygoodguy presented the following explanation :-

> > Hi,
> >
> > I have a spreadsheet set up with months and revenue across columns,
> > i.e:
> >
> > July;August;September;Oct....Total, YTD Total
> > 70000;65000;80000, 72000...Total, YTD Total
> >
> > I also have a cell (I6) that updates and displays the current month
> when
> > logged in. Assuming the months start at A1 and data on A2 - how would
> I
> > go about creating a formula that only totals the YTD figure based on
> the
> > current date? I.e. if the current month is November, the sum total
> for
> > YTD only includes July, Aug, Sep, Oct, and Nov. (Please note the
> fiscal
> > year starts 01 July).
> >

> > Thanks in advance. Please advise if you require any further details-

>
> If I understand correctly, months are arrange left to right starting
> with Jul and ending with Jun. In the YTD column, simply sum the other
> columns. Those not yet used (Dec-Jun) will be empty and so not affect
> the totals. I assume you are totalling the months at the bottom and so
> the same formula will work there as well as individual rows.
>
> --
> Garry
>
>

> Hi Garry,

> If I understand correctly, months are arrange left to right starting

> with Jul and ending with Jun. = Correct

>
> Those not yet used (Dec-Jun) will be empty and so not affect
> the totals. I assume you are totalling the months at the bottom and so

> the same formula will work there as well as individual rows. = yes, but
> what happens next month when you open the spreadsheet and choose the
> following month (i.e. Jan) - I would like the YTD to sum the next month
> (i.e. Dec) automatically. Is this possible?
>
> Cheers




--
garygoodguy

[GS]

The formulas should automatically update as you add values (assuming
Calculation mode is set to Automatic).

Alternative approach:
You could set up your months to collect values from an 'Amount' column
by date if you record dates for each revenue transaction. This is how
my bookkeeping app works, so transaction entries can be input at random
and each month column only collects entries for their respective month.

YTD is done on a 'Summary' sheet that's set up like a P&L so it shows
revenue, COGS, and Expenses by month. Totals are by month, quarter, and
YTD. This sheet collects values from 'Income' and 'Expenses', and so is
read only. YTD is by row for each section. Detail is handled via
Outlining for each Expense category, where sub categories display their
respective amounts. Detail for the Revenue sub categories are handled
the same way, though there's only one revenue category.