Changing formula totals

  • Thread starter Thread starter harwookf
  • Start date Start date
H

harwookf

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.
 
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
 
Many thanks Roger - managed to modify the formula slightly so that it now
does exactly what I wanted.

Your help was much appreciated.
 
Back
Top