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

• ### 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

• 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")

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 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")