Countif and multiple ranges
 

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

Thanks,

M.A.Tyler

Countif and multiple ranges
 

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

No.

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

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler

Countif and multiple ranges
 

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.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)
Can countif check multiple ranges?

No.

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

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler

Countif and multiple ranges
 

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

On 28 mar, 04:48, "T. Valko" wrote:
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)
Can countif check multiple ranges?

No.

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

--
Biff
Microsoft Excel MVP

"M.A.Tyler" <Great Lakes State wrote in ...



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

Thanks,

M.A.Tyler- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Countif and multiple ranges
 

The criteria is text.

"T. Valko" wrote:

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

No.

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

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler

Countif and multiple ranges
 

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"))

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
The criteria is text.

"T. Valko" wrote:

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

No.

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

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler

Countif and multiple ranges
 

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.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
The criteria is text.

"T. Valko" wrote:

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

No.

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

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler

Countif and multiple ranges
 

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"))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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"))

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
The criteria is text.

"T. Valko" wrote:

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

No.

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

--
Biff
Microsoft Excel MVP


"M.A.Tyler" <Great Lakes State wrote in message
...
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2)

Thanks,

M.A.Tyler