Help with Frequency/Array Formula!

[Henrybukowski]

Hi folks,

The idea is to have a formula to count the number of rows(clients) who have at least 1 or more of the options:
Anxiety
Depression
Bipolar

I have the following which works a treat:

=COUNT(1/FREQUENCY(IF(ISNUMBER(SEARCH({"Anxiety","Depression","Bipolar Disorder"},C2:C14&D2:D14&E2:E14&F2:F14)),ROW(C2:C14)),ROW(C2:C14)))

I tried to extrapolate from this to a bigger more complex formula and it doesn't work:

=COUNT(1/FREQUENCY(IF(ISNUMBER(SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},'Data Sheet'!S6:AA6&'Data Sheet'!S7:'Data Sheet'!AA7&'Data Sheet'!S8:AA8&'Data Sheet'!S9:AA9&'Data Sheet'!S10:AA10&'Data Sheet'!S11:AA11&'Data Sheet'!S12:AA12&'Data Sheet'!S13:AA13&'Data Sheet'!S14:AA14&'Data Sheet'!S15:AA15&'Data Sheet'!S16:AA16&'Data Sheet'!S17:AA17&'Data Sheet'!S18:AA18&'Data Sheet'!S19:AA19&'Data Sheet'!S20:AA20&'Data Sheet'!S21:AA21&'Data Sheet'!S22:AA22&'Data Sheet'!S23:AA23&'Data Sheet'!S24:AA24&'Data Sheet'!S25:AA25&'Data Sheet'!S26:AA26&'Data Sheet'!S27:AA27&'Data Sheet'!S28:AA28&'Data Sheet'!S29:AA29&'Data Sheet'!S30:AA30&'Data Sheet'!S31:AA31)),ROW('Data Sheet'!S6:AA6)),ROW('Data Sheet'!S7:AA7)))

Could anybody suggest why this would be totaling to 0 pick up lots of results in my data sheet?
I suspect I have messed up the bins!

Thanks in advance,

Henry

 

[RoryA]

Cross-post here.

 

[Henrybukowski]

Is it not possible to edit posts and add in links on mrexcel? I have been attempting to do so since subsequently posting on excelforum but cannot find the option to do so.

 

[RoryA]

You have a ten minute window in which to edit your post. Thereafter you would have to add an update post.