Count Formula

[Harsha]

Hi Sir,


Am working on Rating Report for the Employees.


Following is the Table Details:-


Emp ID# /Emp Name /Dept /Supervisor /DOjoining /Exp /Ratings

1 A FINANCE P X 2 2.25

2 B HR Q X 4 3.50

3 C Engineer R X 6 4.75

4 D Engineer S X 8 4.00

5 E FINANCE X X 10 3.00


in the next sheet i need Autoupdate formula which shows for ever one Dept how many employees comes under 0 rating or 1 rating or 2 Rating Respectevly Individually


For example :


Under Dept Finance they are 1 person comes under 2.25 Rating

2 person comes under 3.00 Rating LIKE that


But i need the same in autoupdate Formula...


Kindly Help me on this..............


Harsha.

 

[oldchippy]

Hi Harsha,


One way is with SUMPRODUCT assumming your data is on Sheet1 and the formula on Sheet2


=SUMPRODUCT(--(Sheet1!C2:C6="Finance"),--(Sheet1!G2:G6<=3)) adjust the ranges to suit yours

 

[Harsha]

Hi Sir,


here i need how many (counts) Emp under finance, HR, Emps respectively comes under rating 2 or 3 or 4 respectively....


Please......

 

[Hui]

Harsha

You could also use a Pivot Table

and use Count instead of Sum for the main field

Have your Department on one axis and Rating on the other

You can group your Ratings however you want

 

[Harsha]

Thanks for oldchippy and ninja


Harsha.