Lookup + SUM

[ExcelGSC]

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?

Thanks in advance!

 

[steve the fish]

Hi. Have a look at SUMIF. It will give you what you need.

 

[tygrrboi]

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]

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]

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]

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

 

[steve the fish]

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

 

[steve the fish]

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)