+ Reply to Thread
Results 1 to 5 of 5

Product of table row based on column header values

  1. #1
    Registered User
    Join Date
    06-19-2015
    Location
    Oslo
    MS-Off Ver
    2016
    Posts
    21

    Product of table row based on column header values

    Hi

    Attached, I have an Excel 2013 sheet with a toy model of the problem in another much lagrer workbook.

    In the table, I want to take the product of the numbers in the columns "1" and "2" for the row with ID "b". I use INDEX to search for the row with ID "b": INDEX( Table1; MATCH( "b", Table1[ID], 0 ), 0 ). This returns the whole row as a range; ("b", 50%, 50% ).

    Then I thought I could use an array formula to make a range (0, 1, 1) from the column headers, like this:
    ( Table1[#Headers] = "1") + ( Table1[#Headers] = "2" )

    Inside a formula that can take as input an array formula, like MATCH( 1; ( Table1[#Headers] = "1") + ( Table1[#Headers] = "2" ); 0), this input makes sense.

    Now I would like to take the product of my two ranges ( "b", 150%, 150%) and ( 0, 1, 1 ) to get (0, 150%, 150%), and somehow take the product of the non-empty values.

    It would be great if someone could fill in the neccessary steps. Or perhaps there is another more effective approach. Thanks in advance!
    Attached Files Attached Files
    Last edited by p3rlend; 02-15-2016 at 01:22 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,408

    Re: Product of table row based on column header values

    If you 'd like to get the % with criterias from ID and 1 or 2 (headers)
    =SUMPRODUCT((Table1[ID]="b")*(--Table1[[#Headers],[1]:[2]]=2)*Table1[[1]:[2]])
    Quang PT

  3. #3
    Registered User
    Join Date
    06-19-2015
    Location
    Oslo
    MS-Off Ver
    2016
    Posts
    21

    Re: Product of table row based on column header values

    Thank you! That looks smooth.

    But, I need to multiply the 150% in column 1 with the 150% in column 2. So the result should be 225%. So what I need is to have multiple criterias for the header, to have both 1 and 2 included (or even more if there is many columns), not just one of them. And in the end multiply them.

    Thanks again!

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,408

    Re: Product of table row based on column header values

    So for "b":

    =PRODUCT(INDEX(Table1[[#All],[1]:[2]],MATCH("b",Table1[[#All],[ID]],0),0))

  5. #5
    Registered User
    Join Date
    06-19-2015
    Location
    Oslo
    MS-Off Ver
    2016
    Posts
    21

    Re: Product of table row based on column header values

    Thank you :D

+ 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. Selection of Excel table column based on a header
    By Alpana Savarna in forum Excel General
    Replies: 3
    Last Post: 10-16-2014, 09:43 AM
  2. [SOLVED] Find Row in Table based on Column Header
    By thisguy4000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2014, 12:55 PM
  3. Look up row header based on column and table answer
    By jherbert81 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2014, 05:58 PM
  4. Changing column values based on header values
    By CXsjr in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2013, 12:11 PM
  5. Replies: 5
    Last Post: 10-14-2012, 07:02 PM
  6. [SOLVED] Returning all the values in a column based on the column header
    By Benisato in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2012, 11:19 AM
  7. Replies: 0
    Last Post: 09-28-2012, 07:55 AM

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