# Sumifs when trying to match month only in 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.

• I fixed, it was the formatting. – OneEightLeft Sep 13 '16 at 13:22
• I just did a major update to my answer – Rubén Sep 13 '16 at 14:11
• If you've got a fix, it would be really good idea to add your own answer. That'll act as a signpost for any future readers with the same issue. – ale Sep 13 '16 at 14:12
• By the way, this is an outstanding example of a Google Sheets question. You've got the sample data in the post, not an image. You've explained what you're trying to do. You have included the formulas you've already tried and how they didn't work. If only all of the questions we got were like this. – ale Sep 13 '16 at 14:13

Try

=ArrayFormula(

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