# Formula to display column header as result when any value exists in cells under column

1. ## Formula to display column header as result when any value exists in cells under column

Hi

I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.

I started the formula in P2 of the GL Detail-2012 tab. File attached.

Here is what I started: =INDEX(\$M\$1:\$O\$1,SUMPRODUCT(COUNTIF(\$M\$2:\$O\$67756,))). Not working.

Thank you very much.
John

3. ## Re: Formula to display column header as result when any value exists in cells under column

Thanks.

Tried the =INDEX(\$M\$1:\$O\$1, SUMPRODUCT(COUNTIF(\$M2:\$O2, "< > 0")))

with multiple variations of ; or , but cant get it to work...?

4. ## Re: Formula to display column header as result when any value exists in cells under column

If I delete the calculated 0 value from each of other 2 cells in each row, it works. so I need a formula to convert all 0 value amounts to blank, empty cells.

5. ## Re: Formula to display column header as result when any value exists in cells under column

formula does not work. if cells are blank, or have a zero value the wrong answer results.

6. ## Re: Formula to display column header as result when any value exists in cells under column

Swirsjo01, Good afternoon.

I did an example for you.
25-07-2014_ExcelForum_AR1_AR Aging Report-OK.xlsx

Is it what you want?
I hope it helps.

7. ## Re: Formula to display column header as result when any value exists in cells under column

PERFECT!!!

Thank you so very much. Is there a way I can thank, compensate or repay you? I am new to this site. Not sure of the protocol.

Thanks again. John

8. ## Re: Formula to display column header as result when any value exists in cells under column

John, Good evening.

"...PERFECT!!!
Thank you so very much. Is there a way I can thank, compensate or repay you? I am new to this site. Not sure of the protocol.
Thanks again...
"

The above message is my payment.
This is a place for collaboration.

I'm happy to have helped you in your work.
Have a great weekend.

There are currently 1 users browsing this thread. (0 members and 1 guests)