yogi_If (Columns 1 2 and 3 Are Set To1) Or (Columns 1 and 2 Are Set To 1 And Column 2 = na) Then Column 4 = 1 Else Column 4 = 0

                         Google Spreadsheet   Post  #1724
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-11-2014
post by Ragabones:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Q0KFJRMLxoU)
Count criteria in columns taking into account different criteria returning true or false

I'm trying to look at the value of 3 columns and tally a 1 or a 0 in a forth column based on if the have a 1 or a 0.  1 or a 0 represent true and false.  This is easy using a simple countifs or the following.

if(C5+D5+E5>3,1,0)

Here's he problem:  1 of the columns can contain text (na).  How do I count multiple columns while taking into account a variety of values and instead of returning the sum return a true or false represented by a 0 or 1?

I've tried a couple of different things including a countifs with an or (countif can't do or).  Closest I got was sumif as follows.

=arrayformula(SUM(IF((C10=1)*(D10=1)+(D10="na")*(E10=1),1,0)))

But I think if this worked it would just sum the total.

I can share a sheet but think that this should explain while enough to easily recreate.

Thanks for the assistance.  Wouldn't be coming here if I hadn't done my research.

---

I'm still pretty stuck on this one.  I found a Query that works better then anything so far.  It is as follows:

=QUERY(A2:C2, "SELECT COUNT(C) WHERE A=1 AND (B=1 OR B='na') AND C=1 LABEL COUNT(C) ''")

The only problem is it does not place a 0 in the column if the criteria returns false.

It does show a really good example of what I am trying to accomplish.

Column1   Column2   Column3   Column4
1              0              1              0
1              na            1              1
1              1              1              1
1              0              0              0

If Columns 1,2 and 3 are set to 1; Column 4 = 1
If Columns 1 and 3 are set to 1 but Column 2 is na; Column 4 = 1

Any other combination keeps Column 4 as 0.

Can anyone assist as I've run out of ideas.

-------------------------------------------------------------------------------------------------------------------------------------------