Changing formula totals
 

I have a spreadsheet with sub-totals which calculate the columns at the
bottom - like below.

April 1.00
May 2.00
August 1.50
September 3.00
October 5.00
Etc.
Sub-totals:- 12.50

The information for September and October is hidden by using conditional
formatting and will only be visible at the end of the respective month.
Unfortunately though, the information is still showing in the sub-total. In
the example above, I only want the sub-total to show 4.50, then at the end of
September it should be 7.50.

I don't want to have to keep chaning the formula every month to include the
extra line and believe there must be a way to do this.

Thanks in advance.

Changing formula totals
 

Thanks for your help Roger. It works partially.

The formula I have added is:-
=SUMIF(A6:A17,"<="&DATE(YEAR(TODAY()),MONTH(TODAY( )),0),K6:K17)

I have the dates set up as you described in columm A. It worked with what
was on the spreadsheet already and gave the correct totals. However to test
it, I changed September's date from 30/09 to 03/09 to see if it would total.

My conditional formatting worked as it changed and showed the figure in
black, but the totals didn't change.

Have I done something wrong?

Many thanks

"Roger Govier" wrote:

Hi

If you have you values in column A as 30 Apr 2008, 31 May 2008 etc but
FormatCellsNumber Custom mmmm
they will display as April, May etc.

You can then use
=SUMIF(A1:A5,"<="&DATE(YEAR(TODAY()),MONTH(TODAY() ),0),B1:B5)
Change the A1:A5 and B1:B5 ranges to suit

--
Regards
Roger Govier

"harwookf" wrote in message
...
I have a spreadsheet with sub-totals which calculate the columns at the
bottom - like below.

April 1.00
May 2.00
August 1.50
September 3.00
October 5.00
Etc.
Sub-totals:- 12.50

The information for September and October is hidden by using conditional
formatting and will only be visible at the end of the respective month.
Unfortunately though, the information is still showing in the sub-total.
In
the example above, I only want the sub-total to show 4.50, then at the end
of
September it should be 7.50.

I don't want to have to keep chaning the formula every month to include
the
extra line and believe there must be a way to do this.

Thanks in advance.

Changing formula totals
 

Many thanks Roger - managed to modify the formula slightly so that it now
does exactly what I wanted.

Your help was much appreciated.


"Roger Govier" wrote:

Hi

If you have you values in column A as 30 Apr 2008, 31 May 2008 etc but
FormatCellsNumber Custom mmmm
they will display as April, May etc.

You can then use
=SUMIF(A1:A5,"<="&DATE(YEAR(TODAY()),MONTH(TODAY() ),0),B1:B5)
Change the A1:A5 and B1:B5 ranges to suit

--
Regards
Roger Govier

"harwookf" wrote in message
...
I have a spreadsheet with sub-totals which calculate the columns at the
bottom - like below.

April 1.00
May 2.00
August 1.50
September 3.00
October 5.00
Etc.
Sub-totals:- 12.50

The information for September and October is hidden by using conditional
formatting and will only be visible at the end of the respective month.
Unfortunately though, the information is still showing in the sub-total.
In
the example above, I only want the sub-total to show 4.50, then at the end
of
September it should be 7.50.

I don't want to have to keep chaning the formula every month to include
the
extra line and believe there must be a way to do this.

Thanks in advance.