Search code examples
excelexcel-formulaexcel-365

SUMPRODUCT with TEXTSPLIT and MATCH formula returning #VALUE! error when summing multiple intersections in Excel

I am trying to sum values from a table where both row and column references are dynamically generated from concatenated strings in separate cells. Specifically, I need to:

  1. Split and match the values in one cell (BO28) that contains concatenated row references (e.g., "Data Conversion Validation Mock 1, Cutover Plan Validation") to rows in the table.
  2. Split and match the values in another cell (BP28) that contains concatenated column headers (e.g., "Jane Smith, Emily Force") to the column headers in the table.
  3. Sum the intersecting values of each combination of row and column matches.

screenshot of the issue

In my formula, I want to sum the following intersections:

"Data Conversion Validation Mock 1" with "Jane Smith" "Data Conversion Validation Mock 1" with "Emily Force" "Cutover Plan Validation" with "Jane Smith" "Cutover Plan Validation" with "Emily Force"

So, the expected result should be 80 + 40 + 80 + 40 = 240. However, I am getting a #VALUE! error when I try to apply this formula:

=SUMPRODUCT(
    ISNUMBER(MATCH(BN2:BN10, TEXTSPLIT(BN25, ", "), 0)) *
    ISNUMBER(MATCH(BN1:BU1, TEXTSPLIT(BN26, ", "), 0)) *
    BN2:BU10
)

I have used TEXTSPLIT to break the concatenated strings in BO28 and BP28 into individual values. I applied MATCH to find the respective rows and columns, Used INDEX to return the intersecting values. Wrapped it all in SUMPRODUCT to handle the array logic.

Despite following this approach, I keep receiving a #VALUE! error, and idk where the problem lies. (Do let me know if ineed to provide the excel file).. Thanks

Solution

  • Here's a slightly different approach:

        =SUM(
CHOOSECOLS(
CHOOSEROWS(BO2:BQ5,XMATCH(TEXTSPLIT(BN10,", ");BN2:BN5)),
XMATCH(TEXTSPLIT(BN9,", "),BO1:BQ1))
)

    Example