View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default nonadjacent selections

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)