Writing a Formula Which Dynamically Generates Results
Sponsor
Excel Formulas Question:
Id like to know whether it is possible to write a dynamic formula based on different kind of data present in a particular column and generates results dynamically which is depending on the data being added?
Eg;
Suppose my coulmn contains a list of items.
Emp_name Earning
A 10
A 20
A 20
B 20
C 30
D 5
D 10
I want to write a formula which dynamically generates the earnings of
employees by taking unique names from the column. Say If a some adds one more Employee E, the total earnings for employee E should automatically get generated at some cell along with his name. Is this possible in excel?
Excel Formulas Answer:
A simple microsoft excel formulas which should fit what you need :
Assuming source data in cols A & B, in row 2 down
In C2:
=IF(A2=”",”",IF(COUNTIF(A$2:A2,A2)>1,”",ROW()))
Leave C1 blank
In D2:
=IF(ROWS($1:1)>COUNT(C:C),”",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
In E2:
=IF(D2=”",”",SUMIF(A:A,D2,B:B))
Select C2:E2, copy down to cover the max expected extent of data in col A,
say down to E500? Minimize/hide away col C. Col D will return a dynamic
unique list of the names from col A, while col E returns the corresponding
total earnings
Rate This Tips:
(No Ratings Yet)
Loading ...Related Excel Tips
Comments
Have another excel answer or questions for this problem ?
Feel free to post it here..
Best Excel Tips
- Calling Macro Visual Basic Procedures of a Different Project
(5.00 out of 5) - Copy from Worksheet (5.00 out of 5)
- Change Worksheet Tab Colors (5.00 out of 5)
- Delete Worksheet (5.00 out of 5)
- Quick Way Determine The First and Last Business Day of the Month (5.00 out of 5)
- How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
- Date Calculation Start Date Minus Year, Month and Days (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
