Count unique entries

[will31]

Hi,

I have a data sheet with the month of operation in A:A, Project ID in B:B and Department in C:C. The same project may appear more than once in B:B with the same month and department.

I need to count only once each project done in a specified month in a specified department. Any ideas how to achieve this??

 

[ayazgreat]

will31, have you tried Pivot table for the same ?

 

[will31]

Hi Ayaz,

I've tried this but I cannot filter unique Project ID's which have multiple comments.

I need a method that does this using formula's really since the people who will be using this sheet are novice excel users.

 

[T. Valko]

Hi,

I have a data sheet with the month of operation in A:A, Project ID in B:B and Department in C:C. The same project may appear more than once in B:B with the same month and department.

I need to count only once each project done in a specified month in a specified department. Any ideas how to achieve this??

Can you post several rows of sample data so we can see what we're dealing with?

10 or 15 rows worth of data should suffice.

 

[aravindhan_31]

you have operation in A:A, Project ID in B:B and Department in C:C.
if you are using a formula where do you want the result to be appeared? Else send the copy of your file with some dummy data to my email mail.aravindhan@gmail.com

 

[Aladin Akyurek]

Hi,

I have a data sheet with the month of operation in A:A, Project ID in B:B and Department in C:C. The same project may appear more than once in B:B with the same month and department.

I need to count only once each project done in a specified month in a specified department. Any ideas how to achieve this??

Assuming that you have dates in column A formatted to display something like Apr-11...

F2: 1-Apr-11 (displayed as: Apr-11)

G2 houses a department of interest.

H2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B2:B100<>"",
    IF(TEXT(A2:A100,"mmmyy")=TEXT(F2,"mmmyy"),
    IF(C2:C100=G2,MATCHB2:B100,B2:B100,0)))),
     ROW(B2:B100)-ROW(B2)+1),1))

 

[aravindhan_31]

this might also help

http://www.mrexcel.com/forum/showthread.php?t=550626

 

[Aladin Akyurek]

Assuming that you have dates in column A formatted to display something like Apr-11...

F2: 1-Apr-11 (displayed as: Apr-11)

G2 houses a department of interest.

H2, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(B2:B100<>"",
    IF(TEXT(A2:A100,"mmmyy")=TEXT(F2,"mmmyy"),
    IF(C2:C100=G2,MATCHB2:B100,B2:B100,0)))),
     ROW(B2:B100)-ROW(B2)+1),1))

A paren is missing...

=SUM(IF(FREQUENCY(IF(B2:B100<>"",
    IF(TEXT(A2:A100,"mmmyy")=TEXT(F2,"mmmyy"),
    IF(C2:C100=G2,MATCH(B2:B100,B2:B100,0)))),
     ROW(B2:B100)-ROW(B2)+1),1))

 

[T. Valko]

Hi,

I have a data sheet with the month of operation in A:A, Project ID in B:B and Department in C:C. The same project may appear more than once in B:B with the same month and department.

I need to count only once each project done in a specified month in a specified department. Any ideas how to achieve this??

Does column A contain dates (maybe formatted to display only the month name) or does column A contain the month names as TEXT entries?

Also, if column A contains dates, do you need the count for the month AND year or just the month?