Forum Discussion
Help with an If And OR formula
Good afternoon, I'm looking for some help with writing a formula. I have an excel sheet that I am trying to write an If then formula with an OR and AND statement built in. Essentially if any cell in column AG through AQ has a check and the number in column DH is less than 1 or if all the cells in column AG-AQ are marked as "Unchecked" and DH is greater than 0 then I want it to return a "Review", otherwise I want it to say "Okay". This is what I have for a formula:
=IF(OR(AND('Weekly Responses'!AG3="Unchecked",'Weekly Responses'!AH3="Unchecked",'Weekly Responses'!AI3="Unchecked",'Weekly Responses'!AJ3="Unchecked", 'Weekly Responses'!AK3="Unchecked", 'Weekly Responses'!AL3="Unchecked", 'Weekly Responses'!AM3="Unchecked", 'Weekly Responses'!AN3="Unchecked", 'Weekly Responses'!AO3="Unchecked", 'Weekly Responses'!AP3="Unchecked", 'Weekly Responses'!AQ3="Unchecked", 'Weekly Responses'!DH3>0), AND('Weekly Responses'!AG3="Checked",'Weekly Responses'!AH3="Checked",'Weekly Responses'!AI3="Checked",'Weekly Responses'!AJ3="Checked", 'Weekly Responses'!AK3="Checked", 'Weekly Responses'!AL3="Checked", 'Weekly Responses'!AM3="Checked", 'Weekly Responses'!AN3="Checked", 'Weekly Responses'!AO3="Checked", 'Weekly Responses'!AP3="Checked", 'Weekly Responses'!AQ3="Checked", 'Weekly Responses'!DH3<1)),"Review","Okay")
I know I am missing something (or not writing a piece correctly) but I am having trouble finding it. Any suggestions?
- Detlef_LewinSilver Contributor
Sarah,
try this:
=IF(SUM((COUNTIFS(AG3:AQ3,{"Checked";"Unchecked"})>={1;11})*COUNTIFS(DH3,{"<1";">0"})),"Review","Okay")