Forum Discussion
pratik0545
Jul 09, 2021Copper 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 corr...
- Jul 09, 2021
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.
HansVogelaar
Jul 09, 2021MVP
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.
pratik0545
Jul 09, 2021Copper Contributor
=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?
- pratik0545Jul 09, 2021Copper Contributoryes I missed, I have excel 2013....
could you provide for excel 2013- HansVogelaarJul 09, 2021MVP
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.
- pratik0545Jul 10, 2021Copper ContributorThanks Hans