nonadjacent selections

[schillm228]

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]

Here's one way...

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

Hope this helps!

 

[Don Guillett]

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

 

[Harlan Grove]

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)

 

[Domenic]

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!