Sumproduct and Counta function

[dsebrin]

Ok I've been messing with this the last 2 nights and just can't figure it out. I'm trying to use a sumproduct function with also referencing a specific cell value. Here is the formula which works

Cell B2->MedZone Efficiency
=SUMPRODUCT(($A$2:$A$417="Y")*($D$2:$D$417=D2)*($F$2:$F$417))/E2

My goal is instead of typing in the 417 to be able to reference it. The cell I need to reference is located in cell B2 of a tab I have named as "Counts". If possible I'd love to do the same with my vlookup formula

Cell C2->Minimums
=VLOOKUP($D$2:$D$417,Efficiency_Query_Part_2!$A$2:$H$210,7,FALSE)

The cell I'd like to reference instead of typing 210 would be cell B3 of a tab I have named as "Counts"

I have attached the spreadsheet, any help would be much appriciated!

[DonkeyOte]

You have a few options -- the most obvious would be to use a Dynamic Named Range, eg revise:

Name: Draft_Rec
RefersTo: =Draft_Rec!$A$2:INDEX(Draft_Rec!$F:$F,MATCH(REPT("Z",255),Draft_Rec!$D:$D))

This creates a range from A2:Fx where x is determined by last text entry in D

You can then use INDEX to draw from specific columns, eg:

B2: =SUMPRODUCT(--(INDEX(Draft_Rec,0,1)="Y"),--(INDEX(Draft_Rec,0,4)=$D2),INDEX(Draft_Rec,0,6))/$E2

That said I would add IMO Sumproducts should not be used in large volumes as performance will be affected and can invariably be avoided using a simple concatenation column, eg:

G2: =D2&":"&A2
copied down

You can then replicate the above Sumproduct utilising the concatenation column and the far more efficient SUMIF function without requiring named ranges (which in themselves can slow performance if over used), eg

B2: =SUMIF($G:$G,$D2&":Y",$F:$F)/$E2

I would strongly advise you avoid using Arrays / Sumproducts in any significant quantity - in XL elegance does not equate to efficiency and efficiency in XL is the name of the game.

[dsebrin]

Thanks a bunch for your quick reply. I haven't dived into it yet but will today and definilty try the concatenation formula, especially because in some cases data will be pushing 30,000+ records on the draft_rec tab. Anyone have a macro that may help and save space?

D

[dsebrin]

The concatinate formula works great and is very efficient. I can't seem to get the subproduct formula to work. It comes up with a #Ref Error?

Here is the formula you said to try
B2: =SUMPRODUCT(--(INDEX(Draft_Rec,0,1)="Y"),--(INDEX(Draft_Rec,0,4)=$D2),INDEX(Draft_Rec,0,6))/$E2

[DonkeyOte]

Can you post the formula you used in the RefersTo section for the named range Draft_Rec ? It should I believe read:

=Draft_Rec!$A$2:INDEX(Draft_Rec!$F:$F,MATCH(REPT("Z",255),Draft_Rec!$D:$D))

Although it shouldn't cause an issue it's maybe worth keeping the named ranges separate from the sheet names, I would normally precede all of my named ranges with underscore, eg: _Draft_Rec