nested array formula to count unique records - adding conditions

[doug firr]

Hi,
I have been posting frequently recently about a CSE formula I am using to count unique records. I now use this formula instead of sumproduct whenever I wish to count unique records.

Apologies for the frequent posts.

I'm hoping that the following question should keep me good for a while!

Here is the formula in question:

=SUM(IF(FREQUENCY(IF(allregs!$C$2:$C$72387>=summary!$I$7, #after Jan 1st (I7 contains a date Jan 1st)
IF(allregs!$C$2:$C$72387<=summary!$J$7, # before or equal to december 31st (J7 is a date 31 december)
IF(ISNUMBER(SEARCH(summary!G9,allregs!$D$2:$D$72387)),# inc all records where column D contains the contents of G9
IF((ISNUMBER(SEARCH("prospect",allregs!$E$2:$E$72387))+(ISNUMBER(SEARCH("care",allregs!$E$2:$E$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))

I'd like to include all records where allregs E2:E72387 contain the string "prospect" PLUS records from the same column that contain the string "care".

I have never used the formula in this way before - in it's current form it does not work "contians an error".

Is it possible to use the formula in this way?

 

[Aladin Akyurek]

Hi,
I have been posting frequently recently about a CSE formula I am using to count unique records. I now use this formula instead of sumproduct whenever I wish to count unique records.

Apologies for the frequent posts.

I'm hoping that the following question should keep me good for a while!

Here is the formula in question:

=SUM(IF(FREQUENCY(IF(allregs!$C$2:$C$72387>=summary!$I$7, #after Jan 1st (I7 contains a date Jan 1st)
IF(allregs!$C$2:$C$72387<=summary!$J$7, # before or equal to december 31st (J7 is a date 31 december)
IF(ISNUMBER(SEARCH(summary!G9,allregs!$D$2:$D$72387)),# inc all records where column D contains the contents of G9
IF((ISNUMBER(SEARCH("prospect",allregs!$E$2:$E$72387))+(ISNUMBER(SEARCH("care",allregs!$E$2:$E$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))

I'd like to include all records where allregs E2:E72387 contain the string "prospect" PLUS records from the same column that contain the string "care".

I have never used the formula in this way before - in it's current form it does not work "contians an error".

Is it possible to use the formula in this way?

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(allregs!$AC$2:$AC$72387<>"",
  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)),
  IF(ISNUMBER(SEARCH("prospect",allregs!$E$2:$E$72387))+
     ISNUMBER(SEARCH("care",allregs!$E$2:$E$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))

Do not remove the first IF in FREQUENCY...

 

[doug firr]

Thank you Aladin again. So I must say that there are no blank rows in column AC - the field where I wish to count unique records

 

[Aladin Akyurek]

Thank you Aladin again.

You are welcome.

So I must say that there are no blank rows in column AC - the field where I wish to count unique records

If no blanks is guaranteed:

=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)),
  IF(ISNUMBER(SEARCH("prospect",allregs!$E$2:$E$72387))+
     ISNUMBER(SEARCH("care",allregs!$E$2:$E$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))