+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    07-22-2014
    Location
    Philadelphia
    MS-Off Ver
    365
    Posts
    6

    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

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,135

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

    wrong answer
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  3. #3
    Registered User
    Join Date
    07-22-2014
    Location
    Philadelphia
    MS-Off Ver
    365
    Posts
    6

    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. #4
    Registered User
    Join Date
    07-22-2014
    Location
    Philadelphia
    MS-Off Ver
    365
    Posts
    6

    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. #5
    Registered User
    Join Date
    07-22-2014
    Location
    Philadelphia
    MS-Off Ver
    365
    Posts
    6

    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. #6
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    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.
    ...If my answer helped you, Please, click on. * Add Reputation (at left)

    Best regards.
    Marcílio Lobão

  7. #7
    Registered User
    Join Date
    07-22-2014
    Location
    Philadelphia
    MS-Off Ver
    365
    Posts
    6

    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. #8
    Valued Forum Contributor
    Join Date
    05-13-2010
    Location
    Belo Horizonte, Brazil
    MS-Off Ver
    Excel 2003; 2007
    Posts
    441

    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.
    Last edited by Mazzaropi; 07-25-2014 at 05:10 PM. Reason: typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 07-22-2014, 08:27 PM
  2. [SOLVED] compare A and C column if match my logic display result in B column
    By vengatvj in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-03-2013, 02:07 AM
  3. [SOLVED] Match a value in a table and print the header of that column if value exists
    By norms in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2013, 06:48 PM
  4. (TABLE) Help writing formula to display column header of a result
    By scarch05 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-15-2011, 04:40 AM
  5. Display result of formula in header?
    By Hubitron2000 in forum Excel General
    Replies: 2
    Last Post: 03-24-2006, 03:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1