google-sheets-formula

Issue while applying Sumifs arrayformula to whole column


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.


Solution

  • You may try:

    =map(B2:B,lambda(Σ,if(Σ="",,sumifs(Sheet1!F:F,Sheet1!A:A,A2,Sheet1!B:B,Σ))))
    

    enter image description here