I am trying to calculate the sum if column B values from 2 sheets match with each other. Here is the scenario:
Match Column B Sheet 2 names with Column B Sheet 1 names
Match Cell A2 (Date) Sheet 2 with Column A Sheet 1 Dates
If both conditions are satisfied, sum the corresponding values from column F for each name in Column C. I have developed this formula that works for one cell:
=SUMIFS('Sheet 1'!F:F, 'Sheet 1'!A2:A, 'Sheet 2'!A2, 'Sheet 1'!B2:B, 'Sheet 2'!B2:B)
I do not want to drag it down for hundreds of rows, so I am trying to wrap it in ArrayFormula like this:
=ARRAYFORMULA(IF(LEN('Sheet 2'!B2:B),
SUMIFS('Sheet 1'!F:F, 'Sheet 1'!A2:A, 'Sheet 2'!A2, 'Sheet 1'!B2:B, 'Sheet 2'!B2:B),
""))
I tried using SUMPRODUCT
, MMULT
, VLOOKUP
but to no avail:
=ARRAYFORMULA(IF(LEN('Sheet 2'!B2:B),
SUMPRODUCT(('Sheet 1'!B2:B = 'Sheet 2'!B2:B) * ('Sheet 1'!A2:A = 'Sheet 2'!A2) * 'Sheet 1'!F2:F),
""))
=ARRAYFORMULA(IF(LEN('Sheet 2'!B2:B),
MMULT((('Sheet 1'!B2:B = 'Sheet 2'!B2:B) * ('Sheet 1'!A2:A = 'Sheet 2'!A2)), 'Sheet 1'!F2:F),
""))
=ARRAYFORMULA(IF(LEN('Sheet 2'!B2:B),
IFERROR(VLOOKUP('Sheet 2'!B2:B & 'Sheet 2'!A2:A,
QUERY({'Sheet 1'!B2:B & 'Sheet 1'!A2, 'Sheet 1'!F2:F}, "SELECT Col2 WHERE Col1 = Col1", 0), 2, FALSE),
""),
""))
But it does not work, for the whole column, here is the sample sheet for review. Any suggestion would be much appreciated.