Excel2003 ... 1st order of the day? Ask assistance from those intimate with
Excel (at least it started out as the 1st order of the day).

As an Excel hack (my Co is actually sending me to an Excel1 class this
Monday) ... I often muddle through the Excel Help Screens & these great
boards to come up with Formulas that work without much regard for how they
work, the order in which they work, or their calculation efficiency ...
Definitely, a recognized short-coming on my part ... so my apologies for
those times when I fail to use the proper Excel jargon in my post.

Above said ... the following is simply beyond my Excel skills.

I created a mini template (4 WorkSheets with 1000-2000 records each) and all
formulas worked fine, but when I expanded the template to the size I need (4
WorkSheets with 10000-40000 records each) ... CALCULATIONS choked. I have
experimented with INDEX/MATCH, SUMPRODUCT, VLOOKUP & SUM(IF ... but have not
been able to write to calculate efficiently. Consequently, CALCULATIONS
continue to choke.

Helper Cols may be an alternative, but since I avoid them like the plague
(another short-coming) I need guidance from this board on what best to do ...
& I will certainly entertain the use of Helper Cols if this is what is
required & if a little instruction is provided.

What I have ... 4 WorkSheets (CCs, SWCs, ACTs & BTs):

Note: ACTs & BTs have no Formulas ... SWCs pulls in 2 Cols from ACTs ...
CCs pulls in & calculates Cols from SWCs & BTs.

CCs 10000 records (Range A1:N10000 ... Row 1 = Header)
SWCs 40000 records (Range A1:AF40000... Row 1 = Header)
ACTs 40000 records (Range A1:G40000 ... Row 1 = Header)
BTs 10000 records (Range A1:J10000 ... Row 1 = Header)

WorkSheet ... CCs ... (10000 records (Range A1:N10000) ... Row 1 = Header)

Formulas in Cols A:M

What I think my €œchoking€� formulas might be ... Looking for options to
simply?:

Col B ... Range (B3:B10000) ... nice €œarray€� formula from this board
(Ragdyer) pulls in €œunique€� records from BTs ... so I do not have to use
€œAdvance Auto-Filter€� ... I am willing to return to use of €œAdvanced
Auto-Filter€� if necessary.

{=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,BTs!$C$2:$C$10 000&""),0)),"",INDEX(IF(ISBLANK(BTs!$C$2:$C$10000) ,"",BTs!$C$2:$C$10000),MATCH(0,COUNTIF(B$2:B2,BTs! $C$2:$C$10000&""),0))) }

Col C ... Range (C2:C10000):

=IF(OR($B2="",ISNA(MATCH(CCs!$B2,BTs!$C$1:$C$10000 ,0))),"",INDEX(BTs!$E$1:$E$10000,MATCH(CCs!$B2,BTs !$C$1:$C$10000,0)))

Col D ... Range (D2:D10000)

=IF($B2="","",IF(OR(ISNA(MATCH(CCs!$B2,BTs!$C$1:$C $10000,0)),INDEX(BTs!$F$1:$F$10000,MATCH(CCs!$B2,B Ts!$C$1:$C$10000,0))=0),"",INDEX(BTs!$F$1:$F$10000 ,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0))))

Col E ... Range (E2:E10000 ... same as Col C ... different target)

Col F ... Range (F2:F10000)

=IF($B2="","",COUNTIF(BTs!$C$2:$C$10000,CCs!$B2)) ... (I think ok?)

Col G ... Range (G2:G10000)

=IF($B2="","",IF(ISERROR(SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<"A"),--(SWCs!$X$2:$X$40000<""),(SWCs!$AB$2:$AB$40000))), "--",SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<"A"),--(SWCs!$X$2:$X$40000<""),(SWCs!$AB$2:$AB$40000))))

Cols H & I ... Cell H2 down & Cell I2 down ... (same as Col G ... different
target)

WorkSheet ... SWCs ... (40000 records (Range A1:AF40000) ... Row 1 = Header)

Formulas in Cols O:AF (excluding Cols Q, T & AA which are Blank)

What I think my €œchoking€� formulas might be ... Looking for options to
simply?:

Col U ... Range (U2:U40000)

=IF(OR($B2="",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000) )=0),"",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000) ))

Col V ... Range (V2:V40000) ... (same as Col U ... different target)

My "Thanks" to all who support these boards & provide so much €œmagic€� ... Kha