Summing 2 columns based on criteria from 3rd column

[cabbey77]

I have found many tips on how to sum with multiple criteria, but my question seems to be backwards.

I have 2 separate worksheets. The first spreadsheet is a schedule with the following columns:

Week # Product 1, Product 2, Product 3, Product 4
1 3 5 4 2
2 3 5 6 3

The second spreadsheet is where the calculations occur:

Week # Usage
1 calc
2 calc

In the Usage column I want it to sum the values in the Products 2 & 3 columns from spreadsheet 1 where the Week# matches the week # in spreadsheet 2.

Clear as mud?

 

[SkipVought]

hi,

FIRST, your source data is no normalized. It adds complexity to calculations if your data is not normalized.

faq68-5287
faq68-5184

Also what VERSION of Excel?

The ideal table structure, Table1...
[tt]
Week Product ProdValue
1 Product 1 3
1 Product 2 5
1 Product 3 4
1 Product 4 2
2 Product 1 3
2 Product 2 5
2 Product 3 6
2 Product 4 3
[/tt]
the solution table structure...
[tt]
week Product 2 Product 3
1 5 4
2 5 6
[/tt]
and the formula, where this table starts in F1, using Structured Table references, Excel 2007+...
[tt]
=SUMIFS(Table1[ProdValue],Table1[Week],$F2,Table1[Product],G$1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, with multiple criteria, I'd be apt to use a query on the NORMALIZED table structure data that I posted, using an IN statement in the WHERE clause.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cabbey77]

Hello,

It's excel 2007, and if I had any control over the data I have to work with, that would be great, unfortunately the powers that be put the production schedule in this format and I have to work with it in this format (spreadsheet 1). This data changes about once every 4 days, so manaually changing the spreadsheet is not an option.

 

[SkipVought]

You do not need to manually change the data.

You can use the data to create a table that will be much easier to analyze and report from, while the current view is horrible!

Give yourself a break!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

This is pretty straightforward if I read the requirements correctly:

=SUMIF(Sheet1!$A$2:$A$100,Sheet2!A2,Sheet1!$C$2:$C$100)+SUMIF(Sheet1!$A$2:$A$100,Sheet2!A2,Sheet1!$D$2:$D$100)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question