A formula

[wjallen14]

I have a table 1 with 4 columns, Column 1 = housing, column 2=projected cost, column 3=actual cost, column 4= difference.  For each column I would like to use the formula =subtotal(109, [projected cost]) but when I put this in for each column it does not total the column and I do not know what I am doing wrong. Can anyone help?

[#2]

@wjallen14  is it possible to upload a copy of the actual spreadsheet?

Do you actually need SUBTOTAL? From what you've written, it sounds like you might do just fine with SUM(A1:A10) (or whatever the column references are)....

And I assume you don't mean you literally put in =SUBTOTAL(109,[projected cost]) for each column.

Come to think of it, if this is a formal formatted Excel Table (capital "T"), I've read just recently in another posting here that SUBTOTAL isn't allowed in Tables. THAT may be the problem.

[#3]

@wjallen14

These are the formula you need.   If you are copying and pasting the first formula it will "stick" with [Projected Cost].   One option is to use the small "fill handle" in the bottom right corner of the selected cell to drag the formula across instead of copy pasting.

[#4]

@Wyn Hopkins 

can I ask another question, I am trying to write an if statement where I have one sheet 2019 and the next sheet 2020 but I keep getting a value error. the following is the formula that I am using: =IF(TEXT(DATE(INT((Dec!H1-1)/12)+2019,MOD(Dec!H1-1,12)+1,1),"mmm yyyy"),DATE(INT((Jan!H1-1)/12)+2019,MOD(Jan!H1-1,12)+1,1),"NO__"). thank you

[#5]

Hi

Did my last answer help?

Could attach an example file with your new question

Thanks

[#6 · Accepted solution]

@Wyn Hopkinsyes it help greatly. for example pf my next question I have a sheet that December 2019 on and now January is coming so my next sheet marked Jan I would like it to say January 2020. This I would like to go on for the next five years. December 2020 would go January 2021 etc.

[#7]

Hi, could you start a new thread with the new question. Thanks