COUNTIF with multiple disjoint ranges, same criteria
 

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


"Kurt Swanson" wrote:

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") + ...
--
© 2005 Kurt Swanson AB

COUNTIF with multiple disjoint ranges, same criteria
 

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


"Ron Coderre" wrote:

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


"Kurt Swanson" wrote:

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") + ...
--
© 2005 Kurt Swanson AB

COUNTIF with multiple disjoint ranges, same criteria
 

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


"Ron Coderre" wrote:

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


"Ron Coderre" wrote:

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


"Kurt Swanson" wrote:

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") + ...
--
© 2005 Kurt Swanson AB

COUNTIF with multiple disjoint ranges, same criteria
 

See my response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Kurt Swanson" wrote in message
...
Ron Coderre writes:
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.

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...
--
© 2005 Kurt Swanson AB

COUNTIF with multiple disjoint ranges, same criteria
 

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)

"Kurt Swanson" wrote in message
...
"Bob Phillips" writes:
See my response.

I did. It didn't work.
--
© 2005 Kurt Swanson AB

COUNTIF with multiple disjoint ranges, same criteria
 

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


"Kurt Swanson" wrote in message
...
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" writes:
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)

"Kurt Swanson" wrote in message
...
"Bob Phillips" writes:
See my response.

I did. It didn't work.
--
© 2005 Kurt Swanson AB



--
© 2005 Kurt Swanson AB