Possible Sumproduct with Or Function
 

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

Would a "helper column" be a possibility?

As per the attached, a column that counts the number of "achievements" then the SUMPRODUCT (or COUNTIFS if using 2007 or later) just looks for those rows with a count of greater than zero.

Any help to you?

Possible Sumproduct with Or Function
 

On Monday, October 22, 2012 2:39:01 PM UTC-5, OllyR wrote:
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









--

OllyR

Try (NOT tested)
=SUMPRODUCT((Role=B33)+(Searching=1)+(Screening= 1)+(Reporting=1))
based on this idea
=SUMPRODUCT(--(H6:H8=5),--(I6:I8=5),--(((J6:J8=5)+(K6:K8=5))0))