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 whe
logged in. Assuming the months start at A1 and data on A2 - how would
go about creating a formula that only totals the YTD figure based on th
current date? I.e. if the current month is November, the sum total fo
YTD only includes July, Aug, Sep, Oct, and Nov. (Please note the fisca
year starts 01 July).

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

 

[GS]

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

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

 

[garygoodguy]

;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 mont when
logged in. Assuming the months start at A1 and data on A2 - how woul I
go about creating a formula that only totals the YTD figure based o the
current date? I.e. if the current month is November, the sum tota for
YTD only includes July, Aug, Sep, Oct, and Nov. (Please note th 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, bu
what happens next month when you open the spreadsheet and choose th
following month (i.e. Jan) - I would like the YTD to sum the next mont
(i.e. Dec) automatically. Is this possible?

Cheer

 

[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.

--
Garry

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