Google Sheets – Using SUMIFS to Match Month Only

google sheets

I'm having issues pulling the correct data out of this Google Sheet, and could use some help.

Here is the dataset

Hours  |   Date  |   Employee
  1    |  8/7/16 |   Kevin
  4    |  9/7/16 |   John
  3    |  9/3/16 |   John

What I'm trying to do is match the month in a table on another sheet to the month in the date column on my example sheet

       |  Kevin  |  John  |
9/1/16 |    0    |    0   |

My formula in those value fields was:

=ArrayFormula(sum((month('Sheet 1'!B2:B)=month(A2))*('Sheet 1'!C2:C="Kevin")*'Sheet 1'!A2:A))

But it's not capturing all the values and I can't really figure out why.

I'd like to write it like this:

=SUMIFS('Sheet 1'!A2:A,'Sheet 1'!C2:C,"Kevin",'Sheet 1'!B2:B, month(A2)=month('Sheet 1'!B2:B))

The problem with this one is I'm not sure how to extract just the month from the B column on Sheet 1.

Best Answer

Short answer

Try

=ArrayFormula(
  SUMIFS(Sheet1!$A$2:$A,Sheet1!$C$2:$C,B$1,month(Sheet1!$B$2:$B), month(A$2))
)

Explanation

Include the month column inside the MONTH function, and adjust the corresponding criteria, then put the whole formula inside ARRAYFORMULA (keyboard shortcut CTRL + Shift + Enter)

Notes: The $ was added to fix the references, and "John" was replaced by the column header, so the formula could be filled right and down without making manual changes.

Alternative 1

This does exactly the same than the above.

=SUM(
  IFERROR(
    FILTER(
      Sheet1!$A$2:$A,
      MONTH(Sheet1!$B$2:$B)=Month($A2),
      Sheet1!$C$2:$C=B$1),
    0)
 )

Alternative 2

This formula will create the result matrix automatically with one formula.

 =ArrayFormula(
   {{"";Date(2016,UNIQUE(FILTER(Month(Sheet1!B2:B),LEN(Sheet1!B2:B))),1)},
     query(
       {Sheet1!A:C,{"Month";Month(Sheet1!B2:B)}},
       "select SUM(Col1) where Col3<>'' group by Col4 pivot Col3",
       1
     )
   }
)
Related Topic