Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif and multiple ranges
Can countif check multiple ranges? something like;
=COUNTIF(A2:I12,A7:I12,A12:I12,J2) Thanks, M.A.Tyler |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple data ranges for countif | Excel Worksheet Functions | |||
countif for multiple ranges and criteria | Excel Discussion (Misc queries) | |||
Using Multiple, Noncontiguous Ranges in COUNTIF? | Excel Discussion (Misc queries) | |||
COUNTIF() With multiple ranges and variables | Excel Discussion (Misc queries) | |||
Countif & ranges consisting of multiple areas | Excel Worksheet Functions |