Possible Sumproduct with Or Function

[OllyR]

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
date is entered into the appropriate cell. Not all members of staf
achieve this at the same time so there will be gaps until they ar
achieved.

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

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

Obviously the formula below will only sum the cells meeting th
criteria, it wont take into account whether someone has Searching o
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 Oll

 

[Don Guillett]

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

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))