+ Reply to Thread
Results 1 to 8 of 8

Sum Values in a column based on three different columns

  1. 09-01-2021, 03:24 PM #1
    Vj Raj
    Vj Raj is offline
    Forum Contributor
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    101

    Sum Values in a column based on three different columns

    Hi,

    This is more of a complex problem than what is stated in the title. I have attached a sample file to explain the problem better. In the file, I am trying to report cost-roll up for 2021 and 2022 for a Program (Program 1). The issue is, this report depends on the jobs (Production) that will be completed in 2021 and 2022 for the same product. ABC is the parent product and BC goes into ABC to make it a final product. BC is made from B and C valued at $500 each. Hence, the total value of ABC is $1000 per qty. But if I use SUMIFS, it adds up all the values for Program 1 and 2021; Program 1 and 2022 giving me a value of $2000 per qty of ABC. I would like to ignore the cost values for BC from the table so that I don't have duplicate costs in my final report.

    The logic I have in mind is to check if value in Column C is in Column B, if yes, sum all values in Column D for unique values in Column A. I need help converting this logic to a formula. My logic could be wrong though.

    The sample data file will have a second sheet "BOM" that shows the relationship between each part. I have similar data but in a much larger data set.

    Please feel free to ask any questions if the above explanation doesn't make sense. Appreciate any help offered.

    thanks in advance

    VJ
    Attached Files Attached Files
    Last edited by Vj Raj; 09-01-2021 at 03:26 PM.
    Reply With QuoteRegister To Reply
  2. 09-01-2021, 03:58 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: Sum Values in a column based on three different columns

    Add a helper column G and add the formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    copied down.

    Then,
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I think that uses the logic you outlined.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 09-01-2021, 04:09 PM #3
    Vj Raj
    Vj Raj is offline
    Forum Contributor
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    101

    Re: Sum Values in a column based on three different columns

    Hi TMS,

    Thanks for your inputs. I really appreciate it.

    It worked just fine with the sample data. I will try to follow this logic in my actual report and update the post if the problem is solved.

    VJ
    Reply With QuoteRegister To Reply
  4. 09-01-2021, 04:46 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,445

    Re: Sum Values in a column based on three different columns

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
  5. 09-01-2021, 05:32 PM #5
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: Sum Values in a column based on three different columns

    A
    B
    C
    D
    E
    F
    1
    		 Job ID Parent Part Cost Program Year
    2
    1000
    		 ABC BC
    1000
    		 Program 1
    2021
    3
    1000
    		 ABC BC
    1000
    		 Program 1
    2021
    4
    1001
    		 BC B
    500
    		 Program 1
    2021
    5
    1001
    		 BC C
    500
    		 Program 1
    2021
    6
    1002
    		 ABC BC
    1000
    		 Program 1
    2022
    7
    1002
    		 ABC BC
    1000
    		 Program 1
    2022
    8
    1003
    		 BC B
    500
    		 Program 1
    2022
    9
    1003
    		 BC C
    5
    		 Program 1
    2022
    10
    11
    12
    13
    2021
    14
    15
    		 Program 1
    $2,000.00



    C15=SUM(IF(FREQUENCY(IF(F2:F9=B13,MATCH(A2:A9&B2:B9&C2:C9,A2:A9&B2:B9&C2:C9,0)),ROW(B2:B9)-ROW(B2)+1),D2:D9))

    Control+shift+enter

    for year 2022 type 2022 in B13
    Reply With QuoteRegister To Reply
  6. 09-02-2021, 08:58 AM #6
    Vj Raj
    Vj Raj is offline
    Forum Contributor
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    101

    Re: Sum Values in a column based on three different columns

    Hi Caracalla,

    Thanks for the formula. I really appreciate the help.

    Just a quick question. The above sample data has only one product. Can I make this work for multiple Products in the same situation? For ex: in my sample data we have ABC as the top level product and it has its child parts. Similar to that, I had 100s of different products in my actual report with the same problem.

    I hope I explained that well.

    VJ
    Reply With QuoteRegister To Reply
  7. 09-02-2021, 09:05 AM #7
    CARACALLA
    CARACALLA is offline
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,781

    Re: Sum Values in a column based on three different columns

    Attach file indicating the result
    Reply With QuoteRegister To Reply
  8. 09-02-2021, 01:16 PM #8
    Vj Raj
    Vj Raj is offline
    Forum Contributor
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    101

    Re: Sum Values in a column based on three different columns

    I am trying to replicate my actual data with sample data to explain the overall problem even better than the previous example did. As soon as I have it ready, I will post it here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA - Creating new column (Columns M) with values based on previous column (Column L)
    By will12321 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2020, 03:36 AM
  2. [SOLVED] Match Values Based on First Column then Between Values in Next Two Columns
    By ntsun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2017, 10:06 AM
  3. [SOLVED] Concatenating values two columns (column B, column C) based on criterion
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2014, 11:31 AM
  4. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  5. Number of unique values in column A based on values in columns B and C
    By Byron720 in forum Excel General
    Replies: 1
    Last Post: 11-16-2013, 05:18 AM
  6. [SOLVED] Sum values in column based on variable values in 3 other columns
    By Winon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-03-2012, 10:13 AM
  7. SUMIF - add values from one column based on values in two other columns
    By goodmike in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-30-2008, 08:04 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1