Sumifs when trying to match month only in sheets

Question

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.

Answer 1

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
     )
   }
)

Answer 2

I tried the first formula, just replacing the hardcoded name by a cell reference, an it works fine.

=ArrayFormula(
  sum((month(Sheet1!$B$2:$B)=month($A2))*(Sheet1!$C$2:$C=B$1)*Sheet1!$A$2:$A)
)

Result

          Kevin    John
8/1/2016    1       0
9/1/2016    0       7

Note: $ was added to be able to fill right and down without having to apply manual adjustments.