Writing a formula which dynamically generates results

[sandeep singh]

Can I write a dynamic formula based on the different kind of data present in
a particular column and which generates results dynamically 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?

Thanks in advance,
Sandeep

 

[Max]

A simple formulas play which should deliver it for you

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

 

[sandeep singh]

Thanks alot !
Can you also tell me how to modify formula, if I want to put the formula in
some other sheet say sheet3 and data is present in sheet1?

Regards,
Sandeep

 

[Max]

With source data in Sheet1's cols A & B, from row 2 down

In Sheet3,

In A2:
=IF(Sheet1!A2="","",IF(COUNTIF(Sheet1!A$2:A2,Sheet1!A2)>1,"",ROW()))
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))

In C2:
=IF(B2="","",SUMIF(Sheet1!A:A,B2,Sheet1!B:B))
Select A2:C2, copy down to cover the max expected extent of data in Sheet1's
col A.
Hide away col A. Cols B & C returns the required results, as before.

 

[Sandeep Singh]

Hey Max,

Thanks alot,

I need a little more help,
How can i use your formula with COUNTIF in place of SUMIF?
Instead of Summing up the elements i simply want to count them?

Rest all things remain as earlier, i.e. data in sheet1, formula in sheet3 etc.

Eg: for col A & B
A B
sa h
sa h
sa y
ba y
baba h
sa y

Result:
The result in sheet3 should be
Count_of_h Count_of_Y
sa 2 2
ba 0 1
baba 1 0


Kindly do help me
Thanks again,

Regards,
Sandeep

 

[Max]

In Sheet3,
With cols A and B populated as before ..
List in C1 across the uniques from col B in Sheet1, ie: h, Y
Then place in C2:
=IF($B2="","",SUMPRODUCT((Sheet1!$A$2:$A$100=$B2)*(Sheet1!$B$2:$B$100=C$1)))
Copy C2 across/fill down. Adapt the ranges to suit.