Join 3,518 readers in helping fund MetaFilter (Hide)


Why can't spreadsheets work like brains?
April 6, 2012 3:15 PM   Subscribe

Former excel magician attempts to use Google spreadsheets, fails. It should be easy to make a function that can tally a monthly total from a column of dates and a column of numbers, right?

I have a google spreadsheet. Column A is dates, column B is numbers. How do I write a function that will show a sum in a cell in column C by month? Basically I want a function that says, "If column A is between this and that date. then add the number from column B."

The easy work around would be to separate out the dates from "4/6/2012" and have a separate column for each then use the IF function on the month name column. Is there a more elegant solution?
posted by frecklefaerie to Computers & Internet (7 answers total) 4 users marked this as a favorite
 
I think there's a way to do this using SUMIF and MONTH, both of which exist in Google Docs. I can't work it out just now, but I'll try a little later.
posted by Gorgik at 3:47 PM on April 6, 2012


When unable to do sth in Google Docs, a workaround I sometimes use is to write the function in Excel and upload the spreadsheet. Presto! The Excel function is converted to the Gdocs equivalent. Saves time (and hair on your head ;-)
posted by juifenasie at 3:58 PM on April 6, 2012 [1 favorite]


Ok, here is the setup.

Column A has the dates
Column B has the values
Column C has the beginning date to start counting from
Column D has the end date date to end counting
Column E has this formula =ArrayFormula(sum((A:A>=C1+0)*(A:A<=D1+0)*(B:B)))

For all the date ranges that you want to get numbers for, enter the beginning and end dates in C & D and make sure the formula for E1 has been filled down to be in the F column.

Make sure your months are correct. For example, don't give the month of June over 30 days or you will get an error.
posted by lampshade at 5:28 PM on April 6, 2012 [1 favorite]


Oh....here is a temp link to the sheet for you if you want to copy the whole thing.

GoogleDocs Link
posted by lampshade at 5:32 PM on April 6, 2012


errr....."E1 has been filled down to be in the F column."

Should be "E1 has been filled down to whatever dates that you have filled in C&D".

or just use the link....it will make sense.
posted by lampshade at 5:39 PM on April 6, 2012


Have you tried using pivot tables for this? It seems like exactly what you need.
posted by prefpara at 8:43 PM on April 7, 2012


>>here is a temp link to the sheet for you if you want to copy the whole thing.

Temp period over. Sheet made private. If you want to reference it, memail me.
posted by lampshade at 4:07 AM on April 10, 2012


« Older Looking for book/author recomm...   |  Aside from a barometer app, wh... Newer »
This thread is closed to new comments.