none
Is it possible to create a formula to return the sum of multiple cells/columns onto another worksheet? RRS feed

  • Question

  • I have a worksheet where I would like to summarize inventory of part numbers. 

    Another worksheet that has a full inventory list. One of the columns is part numbers another is available inventory.

    The problem I have is that on my summary worksheet I can count one but on the full inventory worksheet there are multiple line items with the same exact part number.

    How can I capture all line items with that part number and return the sum of the inventory for that one part number on my summary worksheet that I want to develop?????

    Example

    Summary Worksheet

    P/N         In Inventory

    12345              1

    Full Inventory Worksheet

    Lot           P/N            Description         Condition            Batch           Available Qty

    ABC          12345            Bike Spokes              AI                    6789                  1

    DEF           12345           Bike Spokes              AI                    9998                  7

    DDD          12345           Bike Spokes              AI                     8799                10

    I would love a formula to capture all of those line items the three in the example above based off the part number. So I would like to return the sum of "18" using a formula.

    To add another metric. I need the condition field to equal "AI"

    Wednesday, September 19, 2018 12:19 PM

Answers

  • You can use the SUMIFS function.

    Let's say the P/N is in A2 on the Summary sheet.

    And let's say the layout of the Full Inventory sheet is as in your post.

    In B2 on the Summary sheet:

    =SUMIFS('Full Inventory'!$F$2:$F$100, 'Full Inventory'!$B$2:$B$100, A2, 'Full Inventory'!$D$2:$D$100, "AI")

    Adjust the ranges if necessary.

    The formula can be filled down if you have other P/Ns in A3 and below.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by stillanoob Wednesday, September 19, 2018 2:54 PM
    Wednesday, September 19, 2018 12:46 PM

All replies

  • You can use the SUMIFS function.

    Let's say the P/N is in A2 on the Summary sheet.

    And let's say the layout of the Full Inventory sheet is as in your post.

    In B2 on the Summary sheet:

    =SUMIFS('Full Inventory'!$F$2:$F$100, 'Full Inventory'!$B$2:$B$100, A2, 'Full Inventory'!$D$2:$D$100, "AI")

    Adjust the ranges if necessary.

    The formula can be filled down if you have other P/Ns in A3 and below.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by stillanoob Wednesday, September 19, 2018 2:54 PM
    Wednesday, September 19, 2018 12:46 PM
  • I got it after fooling around a few more times!

    Thank you so much for your help, this is going to help me out tremendously. 

    Wednesday, September 19, 2018 2:55 PM