conditional sumproduct? no idea.

[jake]

Please Help!
On one part of the spreadsheet I have a resource schedule of employees and
what they should be working on during a specific week (columns B&C).

Column A Column B Column C
Jack EAS EAS
Ted Optimum BSN
Carl Twin Labs Optimum
Bill EAS Optimum

On another part of the same spreadsheet I have stages (column Y) and the
price per unit (column Z) for each account (column X)

Column X Column Y Column Z
EAS Shipping 25.50
Optimum Packaging 30.29
Twin Labs Shipping 19.95
BSN Labeling 35.78


So I need to calculate if an account is in the shipping stage what the total
of column Z per week (columns B&C). For Week 1 (column B) it would be 70.95
and for week 2 (column C) it would be 25.50. HOWEVER, the bigger issue is
that all of these fields are constantly changing so the formula needs to be
open for future iterations. I've tried this

=SUM((SUMPRODUCT ((B2:B5=X1)*Z1)),((SUMPRODUCT ((B2:B5=X2)*Z2))...etc.

which will give me the total amount but I can't seem to work in the "if"
condition for the shipping status. Any thoughts? there's gotta be a better
way

 

[Stefi]

Your request isn't clear enough, but this formula calculates costs of
account EAS (X2, I assumed a header in row 1) for the first week (B2:B5) and
for Shipping status (Y2:Y5), return 25.5:

=SUMPRODUCT(--(B2:B5=X2),--(Y2:Y5="Shipping"),Z2:Z5)

Clarify your request for more help!

Regards,
Stefi

„jake†ezt írta:

 

[jake]

Thanks for the help and I know its kind of confusing. That does help, but i
need to calculate costs of all accounts that are in shipping status for the
first week(EAS and Twin Labs). So the return should count EAS twice since it
appears twice in the first week and Twin Labs once - 70.95.

 

[Stefi]

=SUMPRODUCT(--(B2:B5=$X2),--($Y2:$Y5="Shipping"),$Z2:$Z5)*COUNTIF(B:B,$X2)
If you add columns AA, AB, ... for week1, week2,... to your second table,
enter this formula in AA2 and fill it to the right and down as required then
it returns the requested values for shipping.

Regards,
Stefi

„jake†ezt írta: