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" wrote:

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,Sheet 1!A2)1,"",ROW()))
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sandeep singh" wrote:
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