Proper syntax to shorten Countif formula

**small_wonder** · 09-30-2012, 05:07 PM

Hi, I have written the following COUNTIF formula which works, but is long and confusing. Is there any way of shortening and refining the rule? In essence an "error" is to be displayed if any sport is selected more than twice over C2:F2. I keep getting bracket errors if I try to shorten it.

My formula:

=IF(COUNTIF(C2:F2,"Football")>2,"error",IF(COUNTIF(C2:F2,"Fitness")>2,"error",IF(COUNTIF(C2:F2,"Badminton")>2,"error",IF(COUNTIF(C2:F2,"Rugby")>2,"error",""))))

**FDibbins** · 09-30-2012, 05:33 PM

its not that much shorter, but try this...
=IF(OR(COUNTIF(C2:F2,"Football")>2,COUNTIF(C2:F2,"Fitness")>2,COUNTIF(C2:F2,"Badminton")>2,COUNTIF(C2:F2,"Rugby")>2),"error","")

**daddylonglegs** · 09-30-2012, 05:41 PM

Try this "array formula"

=IF(MAX(COUNTIF(C2:F2,C2:F2))>2,"error","")

confirmed with CTRL+SHIFT+ENTER

**small_wonder** · 10-01-2012, 08:33 AM

Thank you FDibbins and daddylonglegs. That's much better. For now I will use FDibbins as I'm not sure how/why the C2:F2,C2:F2 works? I need to understand before I use... although it is super short! Can you please idiot proof daddylonglegs?

**daddylonglegs** · 10-01-2012, 08:52 AM

COUNTIF expects a single value in the criteria part like

=COUNTIF(Range,Criteria)

If the criteria is a range like

=COUNTIF(C2:F2,C2:F2)

Then the result is an array showing 4 values in this case because C2:F2 is 4 cells, the count of C2 values in C2:F2, the count of D2 values in C2:F2, the count of E2 values in C2:F2 and the count of F2 values in C2:F2, so if you had the following in those cells

Apple
Banana
Banana
Banana

=COUNTIF(C2:F2,C2:F2) gives you {1,3,3,3}

so taking the MAX gives you 3.....which corresponds to the maximum number of repeated items in C2:F2.

Of course my formula doesn't check specifically the sports you mention but I assume they are the only likely contents of those cells