SUMIF using two criteria

Question

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?

Answer 1

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.

Answer 2

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.