nested array formula to count unique records - using isnumber(search(...) in a CSE

[doug firr]

I've been editing/hacking formula given to me by the kind people on this forum. My latest edit seems to be failing - I was hoping someone could spot the problem:

SUM(IF(FREQUENCY(IF(allregs!$C$2:$C$72387>=summary!$I$7,IF(allregs!$C$2:$C$72387<=summary!$J$7,IF(ISNUMBER(SEARCH(G9,allregs!$D$2:$D$72387)),MATCH(allregs!$AC$2:$AC$72387,allregs!$AC$2:$AC$72387,0)))),ROW(allregs!$AC$2:$AC$72387)-ROW(allregs!$AC$2)+1),1))

The first two conditions are date ranges (I7 is Jan 1st and J7 is dec 31st). The next condition is saying if you find the string that exists within cell G9, count that record. This differs from the formula originally given to me which was along the lines of:

IF(allregs!$D$2:$D$72387=summary!$G9....

So my guess is that this array formula will not work with isnumber(search(...)

Hope this question is clear to folk I realise it's an eyeball full.

To re-word the problem, I have used this formula for when I am matching an exact condition such as ="somestring". The formula now has to work like =if(isnumber(search("mestrin") as a kind of partial match.

Can anyone see how to correct the formula?

 

[Marcelo Branco]

Maybe....

=SUM(IF(FREQUENCY(IF(allregs!$C$2:$C$72387>=summary!$I$7,IF(allregs!$C$2:$C$72387<=summary!$J$7,IF(ISNUMBER(SEARCH(summary!G9,allregs!$D$2:$D$72387)),MATCH(allregs!$AC$2:$AC$72387,allregs!$AC$2:$AC$72387,0)))), ROW(allregs!$AC$2:$AC$72387)-ROW(allregs!$AC$2)+1),1))

M.

 

[doug firr]

Thank you again Marcello. I was worried that this beautiful formula would not work when nested with isnumber(...