+ Reply to Thread
Results 1 to 6 of 6

Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

  1. #1
    Registered User
    Join Date
    11-10-2020
    Location
    Texas
    MS-Off Ver
    Excel for 365
    Posts
    3

    Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

    I have a massive spread sheet, single tab, but thousands of lines.
    Multiple customers, various part numbers, and quantities.
    Attached is a very consolidated example.

    I'm trying to zero in on a specific part number, (366102) it will always be that specific part number, take all the quantities for that specified part number, sum them, then multiply them by 25, and that is my Output.
    So in the example sheet, I would expect the output, with nothing filtered, to be 1200.
    That's the base of the formula, but I want the output to change based on the filtered customer.
    So in my example spreadsheet, if I filter by customer Foxtrot, I would expect my output to change from 1200 to 400.
    I hope that makes sense.
    I really appreciate the help.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

    Is it SUMIF(S)?

    =SUMIF(B2:B16,366102,C2:C16)*25

    =SUMIFS(C2:C16,A2:A16,"Foxtrot",B2:B16,366102)*25
    Quang PT

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

    In cell E1, try:

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C16)-ROW(C2),))*(B2:B16=366102))*25

    As you filter the customer, the total will change.

  4. #4
    Registered User
    Join Date
    11-10-2020
    Location
    Texas
    MS-Off Ver
    Excel for 365
    Posts
    3

    Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

    Quote Originally Posted by Gregb11 View Post
    In cell E1, try:

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(C2,ROW(C2:C16)-ROW(C2),))*(B2:B16=366102))*25

    As you filter the customer, the total will change.
    THIS IS IT!!!!

    However...I'm having an issue that I've had on other spreadsheets and I'm not sure how to fix it.
    This formula will be in a "template" with no data.
    When I get the data in a report weekly...I copy from the report and paste values in the template.
    When I do this...the cell with our formula stays at 0.
    But if I go down the column of part numbers, double click in each cell and hit enter, they start to tabulate in our formula cell.
    Or if I manually type in all the part number...works perfect.
    But when the report is over 3000 lines...that's not feasible.
    I've had this before and I've yet to understand why, I've checked the formatting of the cells, it's maddening.
    Do you happen to know how to resolve that?

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

    I'm not sure. Can you attach the file?

  6. #6
    Registered User
    Join Date
    11-10-2020
    Location
    Texas
    MS-Off Ver
    Excel for 365
    Posts
    3

    Re: Formula Help - A specific Part number, sum it's quantities, and multiply by 25.

    Never mind...I figured out how to make it work.
    I have to use Paste Option: Match Destination Formatting.

    Thanks again...that was a big big help.

+ 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. [SOLVED] Formula to add up quantities depending on number of weeks
    By kirval in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2017, 01:27 PM
  2. Replies: 2
    Last Post: 01-27-2017, 05:12 AM
  3. [SOLVED] Summation of Quantities for Duplicate Part Numbers - VBA Help please
    By longbow007 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-09-2016, 12:42 AM
  4. Multiply formula for specific table
    By Rafa100 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-04-2015, 09:06 AM
  5. Is there a formula to split a column into 2 and multiply numeric part?
    By Lauriellen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-12-2015, 12:08 AM
  6. [SOLVED] Compare Part Numbers & Quantities Same Workbook Different Cells
    By agengler11 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-28-2014, 09:52 AM
  7. [SOLVED] Find specific values and multiply them by a number in their column
    By Thomas92W in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-29-2014, 01:22 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