Excel formula to do without work fields

Hi Experts,

I have designed the attached spreadsheet, and am using the “Portion” figures as work fields to be used for generating the “Totals” figures, as you can see by the formulas.  Note that the "Portion" is based on the "Proportion" for that item.

My real data has a lot of rows in the top group, and not many in the bottom group, and I’d like to calculate the “Totals” figures without needing the “Portion” figures at all, if possible.

Any ideas on how I can achieve this?  I’m assuming the “Totals” formulas can be complexified to do this, but I don’t know how.

Ideally I'd like this to work in Excel 2003, but if that can't easily be done, 2007 would be OK.

Thanks.
Tel2
EE1.xls
LVL 12
tel2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pierre HubautCommented:
Hello Tel2,

For a given selected item, the proportion will always be the same (got from the bottom group list);
As a result, for a given item, the formula for Totals will calculate a sum of of terms each being a product of an amount and the constant proportion. You can use this property and rewrite your formula for Totals so to first add all amounts for a given item which is then multiplied by the proportion for this given item as follow:
Cell B7: =SUMIF($A$2:$A$4,"="&$A7,Sheet1!$B$2:$B$4)*$C7
Copy this formula to B8.
Portion is not used and can be deleted then -
This is illustrated by your amended spreadsheet  attached

Pierre
EE1.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tel2Author Commented:
Looks great (at first glance), Pierre!

Not bad for someone who's answered 1 question in almost 12 years of membership!

Well thought through and explained.

I plan to check it more thoroughly later and get back to you.

Thanks.
Tel2
tel2Author Commented:
Works beautifully, Pierre.

Thank you very much.

Tel2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.