# Google Sheets - need formula for three cells of 5 that match

I need an excel-style formula for Google Sheets. I have five columns. I want to create a sixth column that contains a formula that examines the cells each row for the 5 columns. Each cell in each row of these five columns contain "yes" "no" and "conditional." I need a formula that says that if 3 of the 5 columns say "yes" or "conditional" then the new, sixth column with the formula says "approved". Thanks!

• should yes,yes,no,conditional say approved ?? or do you need either three yes or three conditional – Gary's Student Oct 4 '16 at 15:39
• "yes, yes, no, conditional, no" would say "approved". – Mary Catherine Oct 4 '16 at 17:02

In Excel use this formula:

=IF(COUNTIF(A1:E1,"no")>2,"rejected",IF(SUM(COUNTIF(A1:E1,{"yes","conditional"}))>=3,"Approved",""))

In Google Sheets It needs to be an array formula:

=ArrayFormula(IF(COUNTIF(A1:E1,"no")>2,"rejected",IF(SUM(COUNTIF(A1:E1,{"yes","conditional"}))>=3,"Approved","")))

The formula will mark as approved or rejected as soon as the criteria is met. If 3 or more 'no's appear it will spit our rejected. Or as soon as 3 of the others appear it will be marked as approved.

• This is great & amazing! One more thing --- how do I make it leave a blank cell in column F if there are some blank cells in columns A-E ? – Mary Catherine Oct 4 '16 at 17:50
• If there are any blanks? If there is one blank, regardless of the other values, you want it to return blank? @MaryCatherine – Scott Craner Oct 4 '16 at 17:52
• And when you say blank, do you mean truly blank, as no formula and no value? Or is there a formula that returns ""? – Scott Craner Oct 4 '16 at 17:53
• This is great & amazing! I am using the Google Sheets Array Formula. One more thing --- Currently I have "Rejected" in after "approved" in the formula, where the "value if false" would be. This is a spreadsheet that gradually gets filled in by people, so there are often blank cells. If not all cells are full - for example, there are two yes, and three blank - it is automatically saying Rejected. Is there any way to add to this formula to put a condition to only say "Rejected" if there are two or more "No"s? – Mary Catherine Oct 4 '16 at 17:55
• Sorry Scott, I was editing my comment before, posted too soon. Thanks for your help! – Mary Catherine Oct 4 '16 at 17:56