2

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.

  • 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
  • 1
    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
2

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

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.