0

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!

2
  • 1
    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
0

In Excel use this formula:

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

![enter image description here


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

![![enter image description here

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.

6
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.