Lookup + SUM

ExcelGSC

New Member
Hi All,

I am facing some trouble creating a formula.

In the first sheet, I have my orders listed. Each order contains the following collums: [Product code] [Product name] [Quantity] [Amount]
In the second sheet, I want to create an overview of the total quantities sold per product code.

Therefore, I need a formula that looks up where the product code appears, and when it appears counts the quantities.
I have tried for an hour now and can't figure out how to do this.

Anyone has an idea how to do this?

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

steve the fish

Well-known Member
Hi. Have a look at SUMIF. It will give you what you need.

tygrrboi

Well-known Member
assuming the following
your product codes are in Sheet1 column A, starting from row 2 to account for the header.. and quantity is in column C...

You also have a table on Sheet2 where you show the overview. Each row of that table on sheet 2 has a unique product code in column A...

=SUMIF(Sheet1!\$A\$2:\$A\$100, A1, Sheet1!\$C\$2:\$C\$100)

ExcelGSC

New Member
Thank you for your reply. Unfortunately it is a bit more complex and I believe SUMIF is insufficient.
The orders per 2 weeks are listed vertically, but the year is horizontally.

I hope this clarifies the situation.

In the

tygrrboi

Well-known Member
I don't think this is a good way to store data for analysis. It's fine for printing. Is it not possible to change how the data is kept?

ExcelGSC

New Member
Unfortunately not, the sheet is also used as a quick reference for management so has to be "easy on the eyes"

steve the fish

Well-known Member
Could you post that piece of data in a format so I can copy it rather that a picture?

steve the fish

Well-known Member
This is a bit bodgy but works so long as the sheet remains as is:

=SUMPRODUCT(--(\$B\$9:\$M\$12="xx-example1"),\$E\$9:\$P\$12)

Replies
1
Views
361
Replies
0
Views
357
Replies
7
Views
860
Replies
1
Views
245
Replies
2
Views
267

1,191,165
Messages
5,985,034
Members
439,935
Latest member
Monty238

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back