Doge Robert
New Member
- Joined
- Jan 10, 2017
- Messages
- 14
Hello there.
I have a table with budgets for various suppliers and their locations (where they supply, what we're buying).
I also have three different ways of ordering their budgetary numbers into categories. (Suppliers version, our version and governments version)
I'm trying to do sums across the budgetary categories and then write the sum in the topmost row (in the sumcolumn for each category), which match all the criteria.
I've tried illustrating, what I need the sollution to look like in the table, with colorcoding to explain, how each sum is calculated.)
I've tried making a solution with array formulas and indirect (to get the dynamic cell ranges right), but I can't get it to work.
Example of sollution:
In the column BudgetCategory A Sum, I need to calculate the sum of the values in the budgetcolumn, corresponding to the the categories in the BudgetCategory A column for each matching set of supplier and location. So for Supplier A, Location A, Cat A B.
I can do it, but then the sum would appear in every row, where there is a matching set. So three times for Supplier A, Location A, Cat A B.
Is there a way to have the formula calculated for all matching sets but the result printed only in the topmost instance of each matching set?
I can't do it with pivottables, as I need the calculated sums for some other formulas first.
Note also that I'm using a table, as I'll have to add multiple suppliers and locations for each over time, so the ranges must be dynamic.
Is this even possible?
(I'm using Excel in Danish and working with confidential data, so I cannot upload an actual example)
I hope you can help me..
Kind Regards
Robert.
I have a table with budgets for various suppliers and their locations (where they supply, what we're buying).
I also have three different ways of ordering their budgetary numbers into categories. (Suppliers version, our version and governments version)
I'm trying to do sums across the budgetary categories and then write the sum in the topmost row (in the sumcolumn for each category), which match all the criteria.
I've tried illustrating, what I need the sollution to look like in the table, with colorcoding to explain, how each sum is calculated.)
I've tried making a solution with array formulas and indirect (to get the dynamic cell ranges right), but I can't get it to work.
Example of sollution:
In the column BudgetCategory A Sum, I need to calculate the sum of the values in the budgetcolumn, corresponding to the the categories in the BudgetCategory A column for each matching set of supplier and location. So for Supplier A, Location A, Cat A B.
I can do it, but then the sum would appear in every row, where there is a matching set. So three times for Supplier A, Location A, Cat A B.
Is there a way to have the formula calculated for all matching sets but the result printed only in the topmost instance of each matching set?
I can't do it with pivottables, as I need the calculated sums for some other formulas first.
Note also that I'm using a table, as I'll have to add multiple suppliers and locations for each over time, so the ranges must be dynamic.
Is this even possible?
(I'm using Excel in Danish and working with confidential data, so I cannot upload an actual example)
I hope you can help me..
Kind Regards
Robert.