as in the above picture, I need to sum all the values within the table C8:V22 which match

1. the first three characters of column B(orange),
2. row 5 for the number(blue),
3. row 6 for the first character(green),
4. row 7 for the first three characters(yellow)

The desire result would be the sum of the number in red, which is 10001 10081 10161 10221 = 40464

I tried many different ways to write the formula, one of that is:

=INDEX($B$5:$V$22,
  MATCH($D$26,LEFT($B$5:$B$90,3),0),
  MATCH(($F$26=$5:$5) * ($G$26=LEFT($6:$6, 1)) * (H26=LEFT($7:$7,3)), 0))

and pressed Ctrl Shift Enter to make it as an array formula, but couldn't figure out where is the error.

Anyone could help on this? Thank you!

Edit: The following is a simplify table for easy reference:

in this case, the match values are 11 17 26 = 54

I also tried using combinations of functions such as SUMIFS, SUMPRODUCT, search, e.t.c. but still not able figure out the solution.

CodePudding user response：

In your sheet:

=SUMPRODUCT((LEFT(B8:B22,3)=C26&"")*(C5:V5=E26)*(LEFT(C6:V6,1)=F26&"")*(LEFT(C7:V7,3)=G26&"")*C8:V22)

simply retrieve the sum using your parameters and converting in string to match exactly.

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(data,C8:V22,
   ruleColumnB,LEFT(B8:B22,LEN(D26))=TEXT(D26,"0"),
   ruleRow5,C5:V5=F26,
   ruleRow6, LEFT(C6:V6,1)=G26,
   ruleRow7, LEFT(C7:V7,3) =H26,
SUMPRODUCT(data*ruleColumnB*ruleRow5*ruleRow6*ruleRow7)
)
)

Using LET makes the formula more readable - esp. if you name the rules according to your special context.

If you don't have Excel 365 you have to replace the single parameters within SUMPRODUCT with each range/formula