+ Reply to Thread
Results 1 to 6 of 6

Sumproduct to add total qty from several columns

  1. #1
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    199

    Sumproduct to add total qty from several columns

    I've been trying to use sumproduct to get a sum of the total items in house or total items at outside vend.
    I need something that will be able to differentiate from parts in house to parts outside vend and put the total sums into M2 and M4

    thanks in advance for the help.

    Please see the attached sample
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Sumproduct to add total qty from several columns

    Try this...
    =SUMPRODUCT(($A$2:$A$5=1)*($C$1:$N$1="Qty")*($C$2:$N$5)) (in house)
    =SUMPRODUCT(($B$2:$B$5=1)*($C$1:$N$1="Qty")*($C$2:$N$5)) (Vendor)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    199

    Re: Sumproduct to add total qty from several columns

    Thank you very much, it worked perfectly.
    Could you explain the formula for me, I'm a little bit lost on the way it works.

    Thanks

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Sumproduct to add total qty from several columns

    Sure...
    =SUMPRODUCT(($A$2:$A$5=1)*($C$1:$N$1="Qty")*($C$2:$N$5)) (in house)

    the 1st part ($A$2:$A$5=1) tests to see if A contains 1
    For those rows that have 1 in column A, it then checks to see if the heading is "QTY" ($C$1:$N$1="Qty")...this part is to make sure you dont start adding in numeric part numbers..., then adds the relevant values inthe range ($C$2:$N$5)

    Hope that helps?

  5. #5
    Forum Contributor
    Join Date
    05-18-2012
    Location
    Chicopee, Ma
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    199

    Re: Sumproduct to add total qty from several columns

    Thank you , you have been very helpful.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Sumproduct to add total qty from several columns

    Happy to help and 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. Trying to Total Dates using SUMPRODUCT
    By mtnovak2 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-22-2012, 12:07 PM
  2. SUMPRODUCT of total qty sold for each SKU
    By 19magic75 in forum Excel General
    Replies: 2
    Last Post: 06-27-2012, 08:38 AM
  3. Excel 2007 : SUMPRODUCT total
    By bkatzman in forum Excel General
    Replies: 3
    Last Post: 04-29-2010, 11:30 AM
  4. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM
  5. [SOLVED] Sumproduct-total weight of a shipment
    By Esrei in forum Excel General
    Replies: 2
    Last Post: 08-12-2005, 12:05 PM

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