Google sheets countifs+or formula question

akg742

New Member
Joined
Mar 13, 2014
Messages
45
My sheet looks something like the table below, with update columns ending at O. I want to put a formula in B which will automatically label a row as complete or still needs work. The requirements are different, however, for project a/b and c. For a and b, the number of yes cells to be complete is 7. For c, it's 4 but I haven't gotten to that part yet because I keep getting a "error array arguments to countifs are of different size" error message that I don't understand. What am I missing?

=if(or(countifs(C2:O2,"done",a2,"a"),countifs(C2:O2,"done",a2,"b"),countifs(C2:O2,"done",a2,"c"))=7,"done","needs work")

A​
B​
C​
D​
E
project​
updates needed?​
update 1​
update 2​
update 3​
a​
yes​
yes​
no​
b​
no​
yes​
yes
c​
yes​
yes​
yes​
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
May be you are trying for this:

=IF(IF(OR(A2="A",A2="B",A2="C"),COUNTIFS(C2:O2,"Yes"))=7,"Done","Needs work")
 
Upvote 0
May be you are trying for this:

=IF(IF(OR(A2="A",A2="B",A2="C"),COUNTIFS(C2:O2,"Yes"))=7,"Done","Needs work")
This mostly works so thank you. Unfortunately, A and B need to equal 7 but C only needs to get to 4 and I'm not sure where to add in the -3.
 
Upvote 0
I am sorry, i was at work, Please try this
Book3.xlsx
ABCDEFGHIJK
1ProjectUpdates neededUpdate1Update2Update3
2ADoneYesYesYesYesYesYesYes
3BDoneNoYesYesYesYesYesYesYesYes
4CDoneYesYesYesYes
Sheet2
Cell Formulas
RangeFormula
B2:B4B2=IF(COUNTIFS($C2:$O2,"Yes")=LOOKUP(A2,{"A","B","C"},{7,8,4}),"Done","Needs work")
 
Upvote 0
Solution
Since this is Google sheet, Please try this.

=IF(COUNTIFS($C2:$O2,"Yes")=SWITCH($A2, "A", 7, "B", 8, "C", 4), "Done", "Needs work")
 
Upvote 0
Thank you so much! That worked perfectly. Now I need to do some research so I can recreate it in the future.
 
Upvote 0

Forum statistics

Threads
1,226,364
Messages
6,190,539
Members
453,611
Latest member
JRM59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top