+ Reply to Thread
Results 1 to 10 of 10

Extracting multiple results and adding them in separate columns

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    76

    Extracting multiple results and adding them in separate columns

    Hi all,

    Iíve attached a file with a sample of the data that Iím working with.

    The data is in cells K3: U13.

    Iím trying to produce a series of results in cells C3:F6.

    In cells C4:F4 I have a working array function which looks in cells K3:U3 and searches for a value which matches what is in A1. Where it finds that value it will then return the value in cells K4:U4 with the same column number.

    With a value of ďS2Ē in cell reference A1, the first match will be found in cell reference O3. The value in row 4 and the same column number is $56.87 and so this is the value that is returned in cell C4.

    Iím using an expanding reference so that I can copy the formula across to cell reference F4 and, in each successive column, the next occurrence of the value ďS2Ē will be referenced. This all works well.

    However, what I want to be able to do is supplement this formula so that it sums all the values in the referenced column where the value in the Category column matches the value in cell reference A2.

    If I was successful in this formula then the value returned in cell reference C6 would have been 302.16; all the values in cell range O4:O13 where the corresponding value in cell range K4:K13 matches the value in cell reference A2.

    I was expecting that I would use SUMPRODUCT to complete this formula but that is where Iím getting the error. Perhaps SUMPRODUCT isnít the best function to use.
    Any help to complete this would be appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    Re: Extracting multiple results and adding them in separate columns

    If you don't mind to use INDIRECT, please try this formula

    Please Login or Register  to view this content.
    Regards.
    Last edited by menem; 11-10-2019 at 11:42 PM. Reason: Edit formula
    My English is very poor, so please be patient >_<"

  3. #3
    Registered User
    Join Date
    11-10-2019
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Extracting multiple results and adding them in separate columns

    Try the following formular:
    C6=SUMPRODUCT(SUMIF($K$4:$K$13,$A$2,OFFSET($M$4,,AGGREGATE(15,6,COLUMN($A:$H)/($N$3:$U$3=$A$1),COLUMNS($A:A)))))
    Enter only, fill through right more 3 columns.

    Hope it's correct!

    :-)
    Last edited by Edward6266; 11-10-2019 at 11:53 PM.

  4. #4
    Registered User
    Join Date
    08-30-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Extracting multiple results and adding them in separate columns

    Hi Edward,

    Once I changed the Offset from M4 to M3 it worked perfectly. Thanks.

  5. #5
    Registered User
    Join Date
    08-30-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Extracting multiple results and adding them in separate columns

    Hi Edward, further question.

    I'm fairly new to the offset function and I've never used Aggregate before; if I want to change this to a sumifs formula because I want to use 2 criteria (Category and Unit), how would I go about it?

  6. #6
    Registered User
    Join Date
    11-10-2019
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4
    Quote Originally Posted by Wizards View Post
    Hi Edward,

    Once I changed the Offset from M4 to M3 it worked perfectly. Thanks.
    You're welcome!
    I think no need to change from M4 to M3, because the function Sumif() begin from the row 4 (K4), so it need to be balance at M4.

    Regards

  7. #7
    Registered User
    Join Date
    11-10-2019
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4
    Quote Originally Posted by Wizards View Post
    Hi Edward, further question.

    I'm fairly new to the offset function and I've never used Aggregate before; if I want to change this to a sumifs formula because I want to use 2 criteria (Category and Unit), how would I go about it?
    Can you give a sample of criteria you mentioned above.

    Thanks

  8. #8
    Registered User
    Join Date
    08-30-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Extracting multiple results and adding them in separate columns

    It's exactly the same sample as provided previously but also using the range M3:M13 for criteria. For example, I would want to sum those in column O where the criteria 4000 and WA were satisfied.

  9. #9
    Registered User
    Join Date
    11-10-2019
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Extracting multiple results and adding them in separate columns

    Quote Originally Posted by Wizards View Post
    Hi Edward, further question.

    I'm fairly new to the offset function and I've never used Aggregate before; if I want to change this to a sumifs formula because I want to use 2 criteria (Category and Unit), how would I go about it?
    I guess the following formular is what you required:
    Assume that A3="WA"
    Please Login or Register  to view this content.
    Enter only.


    Good night.
    p/s: I can not upload any attachment for your reference.

  10. #10
    Registered User
    Join Date
    08-30-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    76

    Re: Extracting multiple results and adding them in separate columns

    Thanks again - works well.

+ 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. Extracting web results for multiple searches
    By lonctp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-05-2014, 07:03 AM
  2. [SOLVED] Match Multiple Columns from Separate Sheet to get separate column.
    By gvitoro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2014, 03:38 PM
  3. [SOLVED] Coimpare A to B and B to A - results in separate columns
    By Ellen 2Excel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-05-2013, 03:54 PM
  4. Replies: 3
    Last Post: 10-30-2013, 09:18 AM
  5. extracting rows of data from a table that are met by criteria in two separate columns
    By markhocek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2013, 03:20 AM
  6. [SOLVED] Extracting Required Values from one filename into separate columns
    By pyol17 in forum Excel General
    Replies: 14
    Last Post: 08-07-2012, 05:15 AM
  7. Place results in separate columns when exporting to csv
    By concatch in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2011, 04:32 AM

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