+ Reply to Thread
Results 1 to 11 of 11

Sum of like Text in different columns -- probably easy one for once....

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Sum of like Text in different columns -- probably easy one for once....

    Hey guys,

    I am stuck today, I need it done quick and maybe that isn't letting me think clearly....

    I have a list of text in column A with their corresponding quantities in column B

    EX.

    Cat 5
    Dog 22
    Apple 55
    Banana 77

    This same list (different qty. results appears in Columns C and D -- then E & F -- Then G & H )

    I need to Sum all "Cat" quantities found in all columns --

    What would be the most efficient way to do this

    thanks

    -rt

  2. #2
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Sum of like Text in different columns -- probably easy one for once....

    Quote Originally Posted by richtree View Post
    Hey guys,

    I am stuck today, I need it done quick and maybe that isn't letting me think clearly....

    I have a list of text in column A with their corresponding quantities in column B

    EX.

    Cat 5
    Dog 22
    Apple 55
    Banana 77

    This same list (different qty. results appears in Columns C and D -- then E & F -- Then G & H )

    I need to Sum all "Cat" quantities found in all columns --

    What would be the most efficient way to do this

    thanks

    -rt
    ** Note Obviously the are not in same row, and may or may not appear in each column **

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum of like Text in different columns -- probably easy one for once....

    Like this...

    =SUMIF(A2:G5,"cat",B2:H5)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Sum of like Text in different columns -- probably easy one for once....

    Quote Originally Posted by Tony Valko View Post
    Like this...

    =SUMIF(A2:G5,"cat",B2:H5)
    Ok, Obviously I left information out that I have to add for this to be solved.

    The text are long Model numbers that I cannot define as "cat" or "dog" --the list is long and it would take me hours to type in each "cat" -- is there a better way


    For example, column A has X34DD78GHI with a quantity of 77 and Column C has another long list not knowing if X34DD78GHI will be there or not....same is true with column E and G.....
    Last edited by richtree; 07-23-2013 at 11:04 AM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum of like Text in different columns -- probably easy one for once....

    OK RichTree,

    Excel has this old tool still available called Pivot Table Wizard that allows you to use multiple ranges to consolidate values. You will need to add the tool to your QAT and then use it for each pair of columns. See:
    http://www.pivot-table.com/2010/07/1...in-excel-2007/ to add it to the QAT or perhaps the shortcut keys will work.
    Then look at http://www.contextures.com/xlPivot08.html for the multiple consolidated ranges.

    See my example where I've used what I'm suggesting.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum of like Text in different columns -- probably easy one for once....

    Do you already have (or can you create) a list of the unique model numbers?

    X1:X100 = list of unique model numbers

    Then, you'd enter this formula in Y1 and copy down to Y100:

    =SUMIF(A$2:G$5,X1,B$2:H$5)

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sum of like Text in different columns -- probably easy one for once....

    Hi Tony,

    My suggestion below will create that list of unique model numbers when it does the Pivot..

  8. #8
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Sum of like Text in different columns -- probably easy one for once....

    Quote Originally Posted by MarvinP View Post
    OK RichTree,

    Excel has this old tool still available called Pivot Table Wizard that allows you to use multiple ranges to consolidate values. You will need to add the tool to your QAT and then use it for each pair of columns. See:
    http://www.pivot-table.com/2010/07/1...in-excel-2007/ to add it to the QAT or perhaps the shortcut keys will work.
    Then look at http://www.contextures.com/xlPivot08.html for the multiple consolidated ranges.

    See my example where I've used what I'm suggesting.
    Just got the wizard onto my tool bar....gonna head to lunch and work on the table/chart when I am back....back in a hour

  9. #9
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Sum of like Text in different columns -- probably easy one for once....

    I am not sure if all model numbers are available., plus the models are not static as they are dynamic since new model can be added at any time.....


    I will try the pivot first., be back like I said earlier., thanks again

  10. #10
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Sum of like Text in different columns -- probably easy one for once....

    Thanks guys....this worked....

    I have 2010 Excel now and the steps were very different, and it isn't easy to figure out.....

    I also had to change all the headings to match (with others I wanted to consolidate) and I am sure I didn't do it the best way but it worked...


    I will mark as solved., unless anything to add...

    thank you again


    -rt

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum of like Text in different columns -- probably easy one for once....

    Good deal. Thanks for the feedback!

+ 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. Easy way to take data from multiple columns
    By tigerlilygirl in forum Excel General
    Replies: 5
    Last Post: 10-26-2006, 03:58 PM
  2. Easy / Quick way to select 2 columns, 100's of rows?
    By DavidJ726 in forum Excel General
    Replies: 4
    Last Post: 05-19-2006, 02:35 PM
  3. Replies: 3
    Last Post: 04-25-2006, 09:43 AM
  4. [SOLVED] Stack multiple columns into one column... is there an easy way?
    By Julian in forum Excel General
    Replies: 2
    Last Post: 09-16-2005, 03:05 PM
  5. Extract Text--Hopefully Easy!
    By rae820 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-06-2005, 07:05 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