software

Why can't I use nonadjacent selections as a range in the =COUNTIF formula?

For example: =COUNTIF(FirstGroup,E53)
where FirstGroup is a nonadjacent selection of cells A10:A20 G10:G20
Here's one way...

=SUMPRODUCT(--(CHOOSE({1,2},A10:A20,G10:G20)=E53))

Hope this helps!

In article gt;,
schillm228 gt; wrote:

gt; Why can't I use nonadjacent selections as a range in the =COUNTIF formula?
gt;
gt; For example: =COUNTIF(FirstGroup,E53)
gt; where FirstGroup is a nonadjacent selection of cells A10:A20 G10:G20
gt;
gt;

try this idea
=SUMPRODUCT(COUNTIF(INDIRECT({quot;a2:a22quot;,quot;c2:c22quot;}), quot;=ffquot;))
=SUMPRODUCT(COUNTIF(INDIRECT({quot;a2:a22quot;,quot;c2:c22quot;}), E7))
--
Don Guillett
SalesAid Software

quot;schillm228quot; gt; wrote in message
...
gt; Why can't I use nonadjacent selections as a range in the =COUNTIF formula?
gt;
gt; For example: =COUNTIF(FirstGroup,E53)
gt; where FirstGroup is a nonadjacent selection of cells A10:A20 G10:G20
gt;
gt;
Domenic wrote...
gt;Here's one way...
gt;
gt;=SUMPRODUCT(--(CHOOSE({1,2},A10:A20,G10:G20)=E53))

I'd figure the OP wants to use the defined name FirstGroup. Your
formula could be rewritten as

=SUMPRODUCT(--(CHOOSE({1,2},INDEX(FirstGroup,0,0,1),
INDEX(FirstGroup,0,0,2))=E53))

However, for counting, there's a more compact solution: FREQUENCY.

=INDEX(FREQUENCY(FirstGroup,E53*{0.99999999999999; 1}),2)gt; I'd figure the OP wants to use the defined name FirstGroup.

Very true...

gt; However, for counting, there's a more compact solution: FREQUENCY.
gt;
gt; =INDEX(FREQUENCY(FirstGroup,E53*{0.99999999999999; 1}),2)

Nice one Harlan!