Forum Discussion
Solved
How do I obtain the corresponding headers of the values that are responsible for a particular sum
How do I obtain the headers (in the row 1) that are responsible for the result obtained in the cell AV3.
Say for all the values that sum up to 780 (cell AV3), sum not exceeding 1000, I want the corresponding name of the headers in the row 1 (TEXT 20, TEXT 21, TEXT 23, TEXT 26, TEXT 28, TEXT 29, TEXT 32) in one cell or may be in the adjacent cells. Test file attached
Kindly help.
The attached version contains a custom VBA function.
The workbook is now a .xlsm workbook, so you will have to allow macros when you open it.
5 Replies
Assuming that you have Excel 2019 or Excel in Microsoft 365:
In AX2, confirmed with Ctrl+Shift+Enter:
=TEXTJOIN(",",TRUE,IF(OFFSET(B2,,,,MATCH(AU2, SUBTOTAL(9,OFFSET(B2,,,,COLUMN($C$1:$AS$1)-2))))>0,OFFSET($B$1,,,,MATCH(AU2, SUBTOTAL(9,OFFSET(B2,,,,COLUMN($C$1:$AS$1)-2)))),""))Fill down.
- =TEXTJOIN(",",TRUE,IF(OFFSET(B2,,,,MATCH(AU2, SUBTOTAL(9,OFFSET(B2,,,,COLUMN($C$1:$AS$1)-2))))>0,OFFSET($B$1,,,,MATCH(AU2, SUBTOTAL(9,OFFSET(B2,,,,COLUMN($C$1:$AS$1)-2)))),"")) #NAME? is the output,, am I missing something?
- yes I missed, I have excel 2013....
could you provide for excel 2013
