1

I am looking for some Excel related help for a formula which I've not been able to figure out myself.

I have a sample list of data in range A1:B7 - Column A is a list of names (e.g. John, Jane, etc) and column B is a list of project codes (e.g. ABC, ABD, Admin, etc), just like this:

   A      B
  John   ABC
  Jane   ABD
  John   Admin
  Jane   ABC
  Mike   ABD
  John   ABC
  Jane   Admin

I want to count the number of unique projects that John and Jane have each worked on but exclude the Admin project.

For example, John has worked twice on project ABC' and Admin, therefore the unique project count for John would be 1.

Jane has worked on projects ABC, ABD and Admin, therefore the unique project count for Jane would be 2.

I've tried all various combinations of sumifs, countifs and pivot tables but not managed to count the unique projects with the exclusion of Admin.

1
  • Welcome to Super User MDunn. Edit the question or post a comment with a link to the image[s] and someone will edit them in for you :)
    – bertieb
    Sep 18, 2015 at 8:54

1 Answer 1

1

Here is how to do it if you have the table sorted by name. I figure you could do that.

=SUM(1/COUNTIF(B1:B4;B1:B4))-IF(COUNTIF(B1:B4;"=Admin")>0;1;0)

You might need to replace ; with , in formula, if your version of excel differs from mine. Make sure to cofirm this formula by pressing CTRL+SHIFT+ENTER, not just ENTER. This counts number of unique entries for B1:B4, excluding Admin. Assuming B1:B4 are only John's project, this would be the number for John.

You probably could do it without sorting your table, but I couldn't figure out how. The links below might help.

I used the following information: here is the link explaining how to count number of unique values in a column. It uses something called array functions. You can read about them here.

UPD: Also see the answer to this question. It discusses how to exclude cell from the range, which should be helpful. You would expand the B1:B4 to the whole column and then exclude those cells which don't have John in the adjacent cell. I couldn't try it out in my Excel 2003.

4
  • You surely mean B1:B7 instead of B1:B4 ? :) +1 from me Sep 18, 2015 at 10:00
  • @duDE no, I mean B1:B4 in the table where those are cells with John's projects. I couldn't figure out how to get John's projects other than have the table sorted and then have B1:B4 for John, B5:B7 for jane, etc. Sep 18, 2015 at 10:08
  • This is one possible solution but not really that workable when the spreadsheet has large amounts of data and various 'names'. I did find a workaround using an IF statement and SUMPRODUCT then drove a pivot from the result. Thanks for your input though :)
    – MDunn
    Sep 18, 2015 at 10:26
  • 1
    @MDunn Glad you found my answer useful. Care to post your solution as an update to your question or as an answer? Sep 18, 2015 at 10:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .