Question in Excel

[geo40]

Can anyone help me with following issue :

On one sheet I've got several costplaces with its projectnumbers. Next to it on the same sheet I've got a table with the percentage of the project what's completed till so far.

Costplace Projectnumber %-completed of work in progress

101 AA 50%
101 AB 40%
201 BA 65%
301 BB 45%
301 DA 75%
301 DB 70%

On the second sheet the divisions are as follows:

Costplace Projects >= 50%

101 1
201 1
301 2

Which formula do I need to create the second sheet ??

 

[xlbo]

With exactly the same format as your "MAX" question on thread68-377198

=SUM((sheet1!A1:A100=A2)*(sheet1!C1:C100>0.5))

Use CTRL + SHIFT + ENTER to submit the formula

assuming costplace in sheet1 A1:A100 and %Complete in sheet1 C1:C100

HTH Rgds
~Geoff~

 

[nasar]

Another way is to add an additional column, with heading say "X" and with the formula =IF(C2>=0.5,1,"&quot.

Then create a pivot table with CostPlace and Sum of X.

This will give you the desired result.