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:
- 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.
- 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.
- Sum the intersecting values of each combination of row and column matches.
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
1
Here’s a slightly different approach:
=SUM(
CHOOSECOLS(
CHOOSEROWS(BO2:BQ5,XMATCH(TEXTSPLIT(BN10,", ");BN2:BN5)),
XMATCH(TEXTSPLIT(BN9,", "),BO1:BQ1))
)
0
You need to compare both vertically and horizontally headers of data. Then use SUMPRODUCT() to sum corresponding values that matches with column headers and row header. Try-
=SUMPRODUCT(MAP(BO2:BU10,LAMBDA(x,
ISNUMBER(SEARCH(INDEX(BN2:BN10,ROW(x)-ROW(BO1)),BN25))*
(ISNUMBER(SEARCH(INDEX(BO1:BU1,1,COLUMN(x)-COLUMN(BN2)),BN26)))))*
(BO2:BU10))
0
Another way
=XMATCH(TRIM(TEXTSPLIT(A12,",")),$A$2:$A$6) to find course names.
=XMATCH(TRIM(TEXTSPLIT(A13,",")),$B$1:$E$1) to find the user names.
I’ve added TRIM just in case there’s a space after the ,.
=INDEX($B$2:$E$6,<course names formula>,<user names formula>) to return the intersections.
Stick it all together and wrap it in a SUM formula:
=SUM(INDEX($B$2:$E$6,XMATCH(TRIM(TEXTSPLIT(A12,",")),$A$2:$A$6),XMATCH(TRIM(TEXTSPLIT(A13,",")),$B$1:$E$1)))
With INDEX and MATCH no need to deal with headers. TRIM remove unnecessary spaces which misguide the functions.
=SUMPRODUCT(INDEX(A1:G8,MATCH(TRANSPOSE(TRIM(TEXTSPLIT(A12,","))),A1:A8,0),MATCH(VALUE(TRIM(TEXTSPLIT(A13,","))),A1:G1,0)))
1