COUNTIF with multiple disjoint ranges, same criteria

  • Thread starter Thread starter Kurt Swanson
  • Start date Start date
K

Kurt Swanson

Is there any way to specify multiple disjoint ranges in one COUNTIF?

I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),"Y")

I want to avoid COUNTIF(A1:C3,"Y") + COUNTIF(D4:F6,"Y") + ...
 
This formula works as long as it is not located within A1:I9

=SUMPRODUCT(--((A1:C3:D4:F6:G7:I9)="Y"))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Actually, the formula you posted also works as long as it is not located
within A1:I9

=COUNTIF((A1:C3:D4:F6:G7:I9),"Y")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Apologies: I just stumbled onto the issue that plagues both formulas:

It "automatically" extends the test range to A1:I9, ignoring the 3
individual ranges.

You may be stuck with the approach you preferred not to use. I'm sure if
one of us finds a better solution it'll be posted.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
I cannot get COUNTIF to work with a non-contiguious range in my XL97, but if
this is something you have to do frequently, you may be interested in the
"List Unique" feature of Jim Cone's commercial Add-in called XL Companion.
It will list and count each and every value in a non-contiguious range. It's
available at.........

http://www.realezsites.com/bus/primitivesoftware

Vaya con Dios,
Chuck, CABGx3
 
=SUMPRODUCT(COUNTIF(INDIRECT({"A1:C3","D4:F6","G7:I9"}),"Y"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Ron Coderre said:
Apologies: I just stumbled onto the issue that plagues both formulas:
Click to expand...
It "automatically" extends the test range to A1:I9, ignoring the 3
individual ranges.
Click to expand...
You may be stuck with the approach you preferred not to use. I'm sure if
one of us finds a better solution it'll be posted.
Click to expand...

I did. I created a function:

Function IfCount(comparator, ParamArray ranges() As Variant)

n = UBound(ranges()) ' Finds the number of range arguments passed to
' the function.
For x = 0 To n
IfCount = IfCount + WorksheetFunction.CountIf(ranges(x),
comparator)
Next ' Adds each of the arguments into the total
sum.
End Function

And now I can use expressions like:

=IFCOUNT("Y",A1:C3,D4:F7,P14:Z26)

etc...
 
See my response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
RE: =SUMPRODUCT(COUNTIF(INDIRECT({"A1:C3","D4:F6","G7:I9"}),"Y"))

Nicely done, Bob! It works fine in my test.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Well, it works for me, and it did for Ron as well I see.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Indeed, after reading this I made an unsuccessful attempt to reproduce
the problem. Your solution does indeed work. My apologies.

I think I;ll stick with my function solution, for a couple reasons:
a) the range definitions are standard entry, not strings (can
drag-select, automatics updates with row/column insertions/deletions,
etc.)
b) the resultant cell entries are shorter

Bob Phillips said:
Well, it works for me, and it did for Ron as well I see.
Bob Phillips
Click to expand...
 
Yeah I can see the attractions of that approach <G>, I am not a fan of
INDIRECT normally, but I just prefer formulae to functions unless there is a
performance hit.

Regards

Bob
 
Back
Top