Extract unique records based on multiple critieria

[MZING81]

Hello Everyone,

I have this formula that works well for what I need it to do. Which is ignore duplicates, and extract unique records based on multiple ciriteria.

{=SUM(--(FREQUENCY(IF((EXTRACTED!$G$4:$G$3000=$B10)*(EXTRACTED!$H$4:$H$3000<>"")*(EXTRACTED!$I$4:$I$3000=$CP$8),MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0)),ROW(INDIRECT("1:"&ROWS(EXTRACTED!$D$4:$D$3000))))>0))}

Here in lies the problem. I have this formula in a bout 400 plus cells, but because it has the INDIRECT portion the calculations get slowed down considerably. Is there anything that I can do to update the formula, to have it calculate faster, I'm willing to change the formula if needed.

-Thanks

 

[MZING81]

I actually miss spoke with the title it should be "counting/summing unique records in an array based on multiple criteria".

 

[Aladin Akyurek]

That does deliver a multiconditional unique record count... I don't think you need INDIRECT...

=SUM(IF(FREQUENCY(
   IF(EXTRACTED!$D$4:$D$3000<>"",
   IF(EXTRACTED!$H$4:$H$3000<>"",
   IF(EXTRACTED!$G$4:$G$3000=$B10,
   IF(EXTRACTED!$I$4:$I$3000=$CP$8,
   MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0))))),
   ROW(EXTRACTED!$D$4:$D$3000)-ROW(EXTRACTED!$D$4)+1),1))

Define Ivec (from integer vector) as referring to:

=ROW(EXTRACTED!$D$4:$D$3000)-ROW(EXTRACTED!$D$4)+1

We can re-write the formula now as:

=SUM(IF(FREQUENCY(
  IF(EXTRACTED!$D$4:$D$3000<>"",
  IF(EXTRACTED!$H$4:$H$3000<>"",
  IF(EXTRACTED!$G$4:$G$3000=$B10,
  IF(EXTRACTED!$I$4:$I$3000=$CP$8,
  MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0))))),
  Ivec),1))

Concatenating a bit...

J2, copied down:

=D4&H4

We can do another re-write...

=SUM(IF(FREQUENCY(
  IF(EXTRACTED!$J$4:$J$3000<>"",
  IF(EXTRACTED!$G$4:$G$3000=$B10,
  IF(EXTRACTED!$I$4:$I$3000=$CP$8,
  MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0))))),
  Ivec),1))

 

[MZING81]

That does deliver a multiconditional unique record count... I don't think you need INDIRECT...

=SUM(IF(FREQUENCY(
   IF(EXTRACTED!$D$4:$D$3000<>"",
   IF(EXTRACTED!$H$4:$H$3000<>"",
   IF(EXTRACTED!$G$4:$G$3000=$B10,
   IF(EXTRACTED!$I$4:$I$3000=$CP$8,
   MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0))))),
   ROW(EXTRACTED!$D$4:$D$3000)-ROW(EXTRACTED!$D$4)+1),1))

Define Ivec (from integer vector) as referring to:

=ROW(EXTRACTED!$D$4:$D$3000)-ROW(EXTRACTED!$D$4)+1

We can re-write the formula now as:

=SUM(IF(FREQUENCY(
  IF(EXTRACTED!$D$4:$D$3000<>"",
  IF(EXTRACTED!$H$4:$H$3000<>"",
  IF(EXTRACTED!$G$4:$G$3000=$B10,
  IF(EXTRACTED!$I$4:$I$3000=$CP$8,
  MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0))))),
  Ivec),1))

Concatenating a bit...

J2, copied down:

=D4&H4

We can do another re-write...

=SUM(IF(FREQUENCY(
  IF(EXTRACTED!$J$4:$J$3000<>"",
  IF(EXTRACTED!$G$4:$G$3000=$B10,
  IF(EXTRACTED!$I$4:$I$3000=$CP$8,
  MATCH(EXTRACTED!$D$4:$D$3000,EXTRACTED!$D$4:$D$3000,0))))),
  Ivec),1))

thank you for the help I'll give it a try!!!