COUNT formula

[will31]

Hi All,

I have a sheet with a type of activity in AH6:AH3000 which can be one of 5 from a drop down. In AB6:AB3000 I have the project ID and in BL6:BL3000 I have some value where activity has occured in that month.

I have a COUNTIFS formula that works:

=COUNTIFS(Detail!$AH$6:$AH$3000,$B10,Detail!BL$6:BL$3000,">"&0)

This counts the number of non-zero cells in BL:BL that have each activity type. However, some projcts have mulitple entries and would like to count as above but only each project ID once.

e.g.

Project ID Activity Jan-08
PPP1000 NNP 1000
PPP1000-1 NNP 200
PPP1000-2 NNP 1500

When the formula runs it would need to count the above as 1 not three. The project ID's are three letters followed by 4 numbers except where where more work is added then they get a dash for each subsequent sub project as above.

Thank in advance

 

[asking]

A bit of guess work here. What has $B10 in your formula got to do with anything?

Is $B10 the criteria for Cell $AB10 or Cell $AH10 ?

I think your formula should be ~

=COUNTIFS(Detail!$AH$6:$AH$3000,$AH10,Detail!BL$6:BL$3000,">"&0)

Or better still ~ enter into Row 6 and whatever column your formula is in and fill down ~

=COUNTIFS(Detail!$AH$6:$AH$3000,$AH6,Detail!BL$6:BL$3000,">"&0)

Save your worksheet before changing anything, then you can always revert.
Have a nice day!

 

[will31]

Thanks for the reply,

The formula I gave works to count the number times of each project has a non-zero entry in the column BL

What I need is an extension whereby only unique project ID's are counted. The first seven characters define the "uniqueness" of the project ID.