software

This formula works as long as it is not located within A1:I9

=SUMPRODUCT(--((A1:C34:F6:G7:I9)=quot;Yquot;))

Does that help?

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

XL2002, WinXP-Proquot;Kurt Swansonquot; wrote:

gt; Is there any way to specify multiple disjoint ranges in one COUNTIF?
gt;
gt; I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),quot;Yquot;)
gt;
gt; I want to avoid COUNTIF(A1:C3,quot;Yquot;) COUNTIF(D4:F6,quot;Yquot;) ...
gt; --
gt; © 2005 Kurt Swanson AB
gt;

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

=COUNTIF((A1:C34:F6:G7:I9),quot;Yquot;)

Does that help?

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

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; This formula works as long as it is not located within A1:I9
gt;
gt; =SUMPRODUCT(--((A1:C34:F6:G7:I9)=quot;Yquot;))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Kurt Swansonquot; wrote:
gt;
gt; gt; Is there any way to specify multiple disjoint ranges in one COUNTIF?
gt; gt;
gt; gt; I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),quot;Yquot;)
gt; gt;
gt; gt; I want to avoid COUNTIF(A1:C3,quot;Yquot;) COUNTIF(D4:F6,quot;Yquot;) ...
gt; gt; --
gt; gt; © 2005 Kurt Swanson AB
gt; gt;

Apologies: I just stumbled onto the issue that plagues both formulas:

It quot;automaticallyquot; 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-Proquot;Ron Coderrequot; wrote:

gt; Actually, the formula you posted also works as long as it is not located
gt; within A1:I9
gt;
gt; =COUNTIF((A1:C34:F6:G7:I9),quot;Yquot;)
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; This formula works as long as it is not located within A1:I9
gt; gt;
gt; gt; =SUMPRODUCT(--((A1:C34:F6:G7:I9)=quot;Yquot;))
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Kurt Swansonquot; wrote:
gt; gt;
gt; gt; gt; Is there any way to specify multiple disjoint ranges in one COUNTIF?
gt; gt; gt;
gt; gt; gt; I.e. something like COUNTIF((A1:C3,D4:F6,G7:I9),quot;Yquot;)
gt; gt; gt;
gt; gt; gt; I want to avoid COUNTIF(A1:C3,quot;Yquot;) COUNTIF(D4:F6,quot;Yquot;) ...
gt; gt; gt; --
gt; gt; gt; © 2005 Kurt Swanson AB
gt; gt; gt;

See my response.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Kurt Swansonquot; gt; wrote in message
...
gt; Ron Coderre gt; writes:
gt; gt; Apologies: I just stumbled onto the issue that plagues both formulas:
gt;
gt; gt; It quot;automaticallyquot; extends the test range to A1:I9, ignoring the 3
gt; gt; individual ranges.
gt;
gt; gt; You may be stuck with the approach you preferred not to use. I'm sure
if
gt; gt; one of us finds a better solution it'll be posted.
gt;
gt; I did. I created a function:
gt;
gt; Function IfCount(comparator, ParamArray ranges() As Variant)
gt;
gt; n = UBound(ranges()) ' Finds the number of range arguments passed to
gt; ' the function.
gt; For x = 0 To n
gt; IfCount = IfCount WorksheetFunction.CountIf(ranges(x),
gt; comparator)
gt; Next ' Adds each of the arguments into the total
gt; sum.
gt; End Function
gt;
gt; And now I can use expressions like:
gt;
gt; =IFCOUNT(quot;Yquot;,A1:C3,D4:F7,P14:Z26)
gt;
gt; etc...
gt; --
gt; © 2005 Kurt Swanson AB
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)

quot;Kurt Swansonquot; gt; wrote in message
...
gt; quot;Bob Phillipsquot; gt; writes:
gt; gt; See my response.
gt;
gt; I did. It didn't work.
gt; --
gt; © 2005 Kurt Swanson AB
Yeah I can see the attractions of that approach lt;Ggt;, I am not a fan of
INDIRECT normally, but I just prefer formulae to functions unless there is a
performance hit.

Regards

Bobquot;Kurt Swansonquot; gt; wrote in message
...
gt; Indeed, after reading this I made an unsuccessful attempt to reproduce
gt; the problem. Your solution does indeed work. My apologies.
gt;
gt; I think I;ll stick with my function solution, for a couple reasons:
gt; a) the range definitions are standard entry, not strings (can
gt; drag-select, automatics updates with row/column insertions/deletions,
gt; etc.)
gt; b) the resultant cell entries are shorter
gt;
gt; quot;Bob Phillipsquot; gt; writes:
gt; gt; Well, it works for me, and it did for Ron as well I see.
gt;
gt; gt; --
gt; gt; HTH
gt;
gt; gt; Bob Phillips
gt;
gt; gt; (remove nothere from email address if mailing direct)
gt;
gt; gt; quot;Kurt Swansonquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; quot;Bob Phillipsquot; gt; writes:
gt; gt;gt;gt; See my response.
gt;
gt; gt;gt; I did. It didn't work.
gt; gt;gt; --
gt; gt;gt; © 2005 Kurt Swanson AB
gt;
gt;
gt;
gt; --
gt; © 2005 Kurt Swanson AB