+ Reply to Thread
Results 1 to 7 of 7

Trying to use formula that adds a total based on similarities between 3 different columns

  1. 08-21-2014, 10:49 AM #1
    dylanvv
    dylanvv is offline
    Registered User
    Join Date
    08-21-2014
    Location
    Iowa
    MS-Off Ver
    2010
    Posts
    19

    Trying to use formula that adds a total based on similarities between 3 different columns

    Item Qty Period Year for Period
    3VX750 1.00 7 2012
    3VX750 1.00 8 2012
    3VX750 1.00 3 2013
    3VX750 2.00 2 2012
    3VX750 2.00 3 2012
    3VX750 2.00 6 2012
    3VX750 2.00 6 2012

    This probably seems like an easy thing to do for all you experts but I'm not quite sure how to do this and I did try looking it up.

    I need a formula that can be utilized throughout my worksheet for adding up the total quantity of an item that is in the same Period, and Year for Period. If I could I would like the end result to be put in new columns. It probably has something to do with consolidation but I'm not exactly sure how to go about doing this. Any help would be greatly appreciated.
    Reply With QuoteRegister To Reply
  2. 08-21-2014, 10:54 AM #2
    sweep
    sweep is offline
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Trying to use formula that adds a total based on similarities between 3 different colu

    Would a pivot table with year for period and period in column labels and qty in values work?
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
    Reply With QuoteRegister To Reply
  3. 08-21-2014, 11:10 AM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to use formula that adds a total based on similarities between 3 different colu

    Something like this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Item
    Qty
    Period
    Year for Period
    ------
    Period
    Year for Period
    Total
    2
    3VX750
    1
    7
    2012
    6
    2012
    4
    3
    3VX750
    1
    8
    2012
    4
    3VX750
    1
    3
    2013
    5
    3VX750
    2
    2
    2012
    6
    3VX750
    2
    3
    2012
    7
    3VX750
    2
    6
    2012
    8
    3VX750
    2
    6
    2012


    This formula entered in H2:

    =SUMIFS(B2:B8,C2:C8,F2,D2:D8,G2)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 08-21-2014, 11:18 AM #4
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Trying to use formula that adds a total based on similarities between 3 different colu

    Please find the attached sheet to see if this is something you can work with.
    Attached Files Attached Files
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  5. 08-21-2014, 11:18 AM #5
    dylanvv
    dylanvv is offline
    Registered User
    Join Date
    08-21-2014
    Location
    Iowa
    MS-Off Ver
    2010
    Posts
    19

    Re: Trying to use formula that adds a total based on similarities between 3 different colu

    Tony, that is what I would want except I would want the item to show up next to the end result as well.
    Last edited by dylanvv; 08-21-2014 at 11:21 AM.
    Reply With QuoteRegister To Reply
  6. 08-21-2014, 11:59 AM #6
    dylanvv
    dylanvv is offline
    Registered User
    Join Date
    08-21-2014
    Location
    Iowa
    MS-Off Ver
    2010
    Posts
    19

    Re: Trying to use formula that adds a total based on similarities between 3 different colu

    Hey stkneer,

    I think your formula could possibly work except I want to be able to have the years listed all in one column, and list all of the items next to the end result as well. With your formula it's not setup exactly the way I would like it to be viewed.
    Reply With QuoteRegister To Reply
  7. 08-21-2014, 01:04 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Trying to use formula that adds a total based on similarities between 3 different colu

    Quote Originally Posted by dylanvv View Post
    Tony, that is what I would want except I would want the item to show up next to the end result as well.
    Not sure I understand what you mean.

    Do you want the total Qty for a specific item during a specific period during a specific year?
    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. Vlookup formula that adds two columns possible?
    By Dopey1956 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 08:16 PM
  2. Help! Can you assist with: The column adds up a total and also adds the date
    By cobrany in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 03-04-2013, 01:16 PM
  3. Finding similarities/differences between columns.
    By johnlennon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2008, 04:58 PM
  4. Similarities in 2 columns
    By auaeman in forum Excel General
    Replies: 3
    Last Post: 10-01-2008, 03:57 PM
  5. Function recognizes two columns and adds total of two other columns
    By grafx77 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-18-2007, 09:09 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1