Workaround for COUNTIF if diagonal cell range?

[kblument]

Hi,
I have a formula that uses COUNTIF that I'm trying to get to work using a diagonal range of cells (CT2,CU3,CV4,CW5....DI17). Unfortunately I've discovered that COUNTIF wont work with such a range. Even creating a named list for this diagonal range doesn't work with COUNTIF.

Here is the formula that works fine if I have a straight column as a range

=IF(AND(COUNTIF(CT2:CT17,1)<2,COUNTIF(CT2:CT17,2)<2,COUNTIF(CT2:CT17,3)<2,COUNTIF(CT2:CT17,4)<2,COUNTIF(CT2:CT17,5)<2,COUNTIF(CT2:CT17,6)<2,COUNTIF(CT2:CT17,7)<2,COUNTIF(CT2:CT17,8)<2,COUNTIF(CT2:CT17,9)<2,COUNTIF(CT2:CT17,10)<2,COUNTIF(CT2:CT17,11)<2,COUNTIF(CT2:CT17,12)<2,COUNTIF(CT2:CT17,13)<2,COUNTIF(CT2:CT17,14)<2,COUNTIF(CT2:CT17,15)<2,COUNTIF(CT2:CT17,16)<2),0,1)

Is there a workaround so I can use the diagonal range?

 

[Scott Huish]

There is a much simpler formula for your straight range:

=AND(COUNTIF(CT2:CT17,ROW(INDIRECT("1:16")))<2)

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Name your diagonalrange, I used diagonalrange is this example.

=AND(FREQUENCY(diagonalrange,ROW(INDIRECT("1:16")))<2)

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[kblument]

Very cool! I never knew ROW and INDIRECT could be used that way. I tried it for the diagonal and at first it didn't work. However I changed the values in quotes from 1:16 to 2:17 and it worked!
Thanks a lot for your help!

 

[Scott Huish]

Glad I could help!