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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
Actually, I just realized A=7, B=8, and C=4 (because I needed this to be more complicated)
 
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,458
Messages
6,191,155
Members
453,643
Latest member
adamb83

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