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 ,
schillm228 wrote:

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

Domenic wrote...
Here's one way...

=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)

I'd figure the OP wants to use the defined name FirstGroup.

Very true...

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

=INDEX(FREQUENCY(FirstGroup,E53*{0.99999999999999; 1}),2)

Nice one Harlan!

try this idea
=SUMPRODUCT(COUNTIF(INDIRECT({"a2:a22","c2:c22"}), "=ff"))
=SUMPRODUCT(COUNTIF(INDIRECT({"a2:a22","c2:c22"}), E7))
--
Don Guillett
SalesAid Software

"schillm228" wrote in message
...
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