2

I have a Google spreadsheet with multiple pages, each tracking a different account. I have a summary page on which I want to display the data in a meaningful way.

On each page I have something like the following:

Column A      Column B        Column C 
date          amount          category

Right now I have this:

=ABS(sumif(SHEET1!C2:C, "Widgets", SHEET1!B2:B)+sumif(SHEET2!C2:C, "Widgets", SHEET2!B2:B)+sumif(SHEET3!C2:C, "Widgets", SHEET3!B2:B))

That gives me a sum of everything spent on Widgets in all accounts. What I can't seem to do is find a way to also tell the spreadsheet to limit the summary to a given date range.

How do I create a cell on the summary page to show the sum total of amounts in all sheets that are in column B which transpired between January 1 and January 31 and represent a given category?

  • Gregory: I don't really understand what you mean with "limit the summary to a given data range", as you restricted the data to Column B if C matches the criterion. – StampedeXV Apr 3 '14 at 10:52
  • What I currently have: the sum total of all the amounts in column B where column C is Widgets. What I'd like is the sum total of all amounts in column B where column C is Widgets, AND where the date in column A is a day in January 2014. – Gregory Apr 5 '14 at 17:38
2

If I am understanding you correctly, you can already get the sum of the amounts for your different sheets, all you need is the date limitation.

This is a relatively simple answer, use FILTER().

You'll have to change the way your summation works, but you can filter out, from each sheet, the exact numbers you need within a specific date range using FILTER. Here's what the formula looks like:

=FILTER(B:B, A:A>=G1, A:A<=G2, C:C="Widgets")

G1 is your starting date, and it needs to be inside of a cell with proper date formatting. G2 is your ending date, also needs to be inside of a cell with proper date formatting.

You can use any actual cell in your summary page to house the dates, I just used G1 and G2 in this case because it was available.

NOTE: You need to change the references to cells accordingly!

The above filter will provide you the entire second column (B) which you can then add together to give you the final number for that sheet.

=SUM(FILTER(B:B, A:A>=G1, A:A<=G2, C:C="Widgets"))

That will give it to you for a single sheet, you can just duplicate it to as many sheets as you need.

  • That doesn't work unless I go to each sheet and add fake data. Not every sheet has a transaction for every category, at least not yet. And not every sheet has a transaction on each day. To make it work, I needed to have every category show up at least once in column C on every page, and I every sheet had to have a transaction for the dates listed in G1 and G2. By adding all those phony transactions to every sheet, I can get the sums to appear on the summary page, but it seems like a "bad" way to do it. Is there perhaps a more graceful solution; one that doesn't involve adding incorrect data? – Gregory Apr 8 '14 at 1:57
  • Wait, I was interpreting your ask from above, I figured you had all categories lined up? Could you provide an example sheet, because if you're looking to create a summary sheet of all amounts for each unique type of category found on each page, then that's a different matter. – JonLim Apr 8 '14 at 2:32
1

With, in your summary sheet, the start date in A1, the end date in A2 and the selected category criterion in A3 then:

=sumifs(Sheet1!B:B,Sheet1!C:C,A3,Sheet1!A:A,">="&A$1,Sheet1!A:A,"<="&A$2)+sumifs(Sheet2!B:B,Sheet2!C:C,A3,Sheet2!A:A,">="&A$1,Sheet2!A:A,"<="&A$2)+sumifs(Sheet3!B:B,Sheet3!C:C,A3,Sheet3!A:A,">="&A$1,Sheet3!A:A,"<="&A$2)  

may suit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.