Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sumproduct? no idea.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sumproduct? no idea.
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: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sumproduct? no idea.
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" wrote: 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: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional sumproduct? no idea.
=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: 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" wrote: 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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
I've no idea how to do this. :( | Excel Worksheet Functions | |||
any idea? | Links and Linking in Excel | |||
any idea?? | Excel Worksheet Functions | |||
Help Please. No Idea what to look for or how to do this | Excel Discussion (Misc queries) |