Countif and multiple ranges

M

M.A.Tyler

Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler
 
T

T. Valko

=COUNTIF(A2:I12,A7:I12,A12:I12,J2)
Can countif check multiple ranges?
Click to expand...

No.

Why do you have overlapping ranges? What's the criteria? If it's numeric you
can use INDEX(FREQUENCY.
 
T

T. Valko

If it's numeric you can use INDEX(FREQUENCY.

Clarificiation:

If it's numeric you *might* be able to use INDEX(FREQUENCY. Depends on the
criteria.
 
J

JB

with UDF:

=CountIfMZ((B3:B7;D4:D9;G2:G9);"kk")

In a module (Alt+F11 Insert/Module):

Function CountIfMZ(champrech As Range, valCherchée)
Application.Volatile
temp = 0
For i = 1 To champrech.Areas.Count
For j = 1 To champrech.Areas(i).Count
If valCherchée = champrech.Areas(i)(j) Then
temp = temp + 1
End If
Next j
Next i
CountIfMZ = temp
End Function

http://cjoint.com/?dEhfvFydvr

JB
http://boisgontierjacques.free.fr
 
T

T. Valko

If you only have a few ranges you're better off stringing a bunch of
COUNTIFs together:

=COUNTIF(A1:C4,"x")+COUNTIF(F5:F7,"x")+COUNTIF(F15:J15,"x")

If you have a lot of ranges you can do something like this:

=SUM(COUNTIF(INDIRECT({"A1:C4","F5:F7","F15:J15"}),"x"))
 
T

T. Valko

P.S.

The big disadvantage to using something like:

=SUM(COUNTIF(INDIRECT({"A1:C4","F5:F7","F15:J15"}),"x"))

The ranges are absolute and will not change if the range changes. For
example, if you drag the range F15:J15 to G2:K2 the formula won't
automatically change to reflect the new range location. It'll still
reference F15:J15. Same thing with new row/column insertions.
 
T

T. Valko

Another option as long as the ranges are the same size and shape:

=SUMPRODUCT(--(CHOOSE({1,2,3},A1:A5,D6:D10,G11:G15)="x"))
 
Top