0

I am not sure if this is the best way to go about this, but I am using the formula below to try and get a count. The formula if giving me the sum of fields M15:V15 that matsh the criteria listed. However I only want to have it display either "zero" for no match or "1" for any single match. So I was dividing by the same formula. This gives me an error because you can not divide 0/0. Any suggestion on how to get this to work the way I need it? again just want to display "1" if there is a match anywhere or "0" if no match at all.

=SUM(COUNTIFS(M15:V15,{"Script NC","Input NC","Critical Script NC","Critical Input NC"}))/SUM(COUNTIFS(M15:V15,{"Script NC","Input NC","Critical Script NC","Critical Input NC"}))

Thanks Paul

Paul
  • 1
  • 2
  • I may have forgotten to mention that the Field that contains "Input NC" and "Critical Input NC" also contain other options which I do not want to count. – Paul Feb 15 '18 at 17:23

1 Answers1

0
=IF(SUM(COUNTIFS(A4:D4,{"Script NC","Input NC","Critical Script NC","Critical Input NC"}))>0,1,0)

Your SUM(Countifs... code appears to do a good job of identifying if any of those values exist in the cells your looking at. I simply added a IF statement that says if this number is not zero then the value is 1. It also avoided the devide by zero issue you're having.

RunThor
  • 169
  • 2
  • 10