 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
done a basic 'google sheets' spreadsheet to help my wife sort her self employment account out.
shes just filling in her return now, and i realised i hadnt got a formula sorted to add all the different sheets up into one grand total. ive done it manually now, but i thought id put a formula in to automatically do it for future years.
the tabs are called January 2017, February 2017 etc etc for the whole year. i googled how to do this and came up with this formula.....
=SUM(January 2017!C17,February 2017!C18,March 2017!C23,April 2017!C27,May 2017!C18,June 2017!C25,July 2017!C25,August 2017!C25,September 2017!C25,October 2017!C25,November 2017!C25,December 2017!C25)
.....where the cell holding the individual total is C17, C18 on next one etc.
however, the cells come up with an error stating "Formula parse error"
i cant see any obvious mistakes, but can you tell me where im going wrong please?
thanks
EDIT: realised later id be better doing april to april rather than jan to jan but i can amend that if i get this formula business sorted.
Why not reduce it to the bare minimum:
=SUM(January 2017!C17)
See if it's correct then just keep adding references until it breaks again.
good thinking. just done that and error at first hurdle 🙂
so...... whats wrong with =SUM(January 2017!C17)
EDIT: considered whether the cell needed formatting to currency, but thats not it either....
Don't know about google docs but wiht Excel if you have spaces in sheet names you need to enclose in ''
So =SUM('January 2017'!C17)
Quotes
now reads =SUM("January 2017"!C17) and still error.
EDIT: got it thanks, i used speech marks instead of quotes. thats worked now so ill try the biggie again. thanks a lot 🙂
Also why use sum when a basic + would do?
^^ this.
='January 2017'!C17+'February 2017'!C17+...
Or, IIRC you can do a formula to add all of them up, try: =sum('January 2017:December 2017'!C17)
Obvs change the date range to be what you want. I think that'll work if they're in the right order. You can also do =sum('*'!C17') I think to sum that cell on every sheet. Maybe.
thanks. problem with that is its not always C17, the cell no changes each page depending on how many lines of work she has.
take the point about the +'s, but maybe much of a muchness, i just went with what google showed me 🙂
all sorted now chaps, thanks a lot.
One minor suggestion would be to put the totals at the top of each months sheet, so that extra lines of work don't move that about.
Or leave plenty of room (C50) so if she does loads that month it isn't moved.
good suggestions, ill incorporate one of them (probs the total at the top) when i create mk2 😉
thanks
Yep as above or do a summary sheet with each months totals on so it's visible from one place and then sum the summary. Massive rambling formula to do multiple steps are a source of errors. Being able to see each of the components and check them is useful.
In which case try =sum('January 2017:December 2017'!(max(C:C))
May need a concatenation to assemble that though.
if you use Excel then you can also name the cells you want to sum, then it's easier to create the summary formula.
=name1 + name2 + name3 ....
"[i]=sum('January 2017:December 2017'!(max(C:C))[/i]"
Won't work if there's both +ve & -ve values in the column, you'll potentially pick up one item rather than the total.