SOLVED

New Contributor

# 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.

5 Replies

# Re: How do I obtain the corresponding headers of the values that are responsible for a particular su

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.

# Re: How do I obtain the corresponding headers of the values that are responsible for a particular su

=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?

# Re: How do I obtain the corresponding headers of the values that are responsible for a particular su

yes I missed, I have excel 2013....
could you provide for excel 2013
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: How do I obtain the corresponding headers of the values that are responsible for a particular su

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.

Thanks Hans