Hi All,

I have tried to figure this out but really am stuck.

I have about 300 staff who are designated AS or RS in one column.

There are also the following columns - Searching, Screening, Comparing, Evaluating and Reporting. As soon as an AS or RS achieves one of these a date is entered into the appropriate cell. Not all members of staff achieve this at the same time so there will be gaps until they are achieved.

I would like to know how many AS's have achieved at least one of Searching, Screening or Reporting. As you can see above some may have achieved all 3 and some may have achieved only 1 however I only need to record if one of these have been achieved.

I have tried using a sumproduct function but this only counts the cells and doesnt give the right result. I have even tried using + as a separator but this doesnt work either.

Obviously the formula below will only sum the cells meeting the criteria, it wont take into account whether someone has Searching or Screening or Reporting but does anyone have any suggestions?

=SUMPRODUCT(--(Role=B33),--(Searching=1),--(Screening=1),--(Reporting=1))

Any help will be greatly appreciated.

Thanks Olly