Another Range Name & SUMPRODUCT Question

[Fujirich]

I'm working with the following formula -

=IF(OR(SelectedCriteria="",SelectedValue=""),"",SUMPRODUCT(--(INDIRECT(SUBSTITUTE(SelectedCriteria," ","_"))=SelectedValue),--(Current_Status=VLOOKUP($D237,Admin!$B$323:$C$327,2,FALSE)),--(Product_Group=VLOOKUP($C$235,Admin!$B$282:$C$289,2,FALSE)),Sales))


My question goes specifically to the last condition in that formula -

--(Product_Group=VLOOKUP($C$235,Admin!$B$282:$C$289,2,FALSE))


My complete product group list is as follows -

Blankets
Digital Printing
EI DDCP
EI Inkjet
EI Inkjet WkFlw
EI News PlateSetr
EI Platesetter
EI Workflow
ENO Film
ENO Plates
Flexo
Fuji CTP News
Fuji CTP Plates
Fuji DDCP
Fuji Film
Fuji PS Plates
Ink
Other
Press Chem
Press Coatings
Processors
Prof. Services
Wide Format


Some of these are defined as single entities. Digital Printing is a good example. But I need to group others and get a total result for a number of these together.

In this regard, I have a named range called "EI" that includes the following -

EI DDCP
EI Inkjet
EI Inkjet WkFlw
EI News PlateSetr
EI Platesetter
EI Workflow


Is there a way I can call for that named range of "EI" from within the SUMPRODUCT formula, and have it return a sum for any and all of those five product groups? Or do I have to define all of those separate group names as their own individual conditions?

Thanks for any ideas!

 

[DonkeyOte]

Hi again, the logic's not exactly clear... ie what is stored in B ?

if you already knew the range containing the values to look for you could use an approach like:

=SUMPRODUCT(--(INDIRECT(SUBSTITUTE(SelectedCriteria," ","_"))=SelectedValue),--(Current_Status=VLOOKUP($D237,Admin!$B$323:$C$327,2,FALSE)),--(ISNUMBER(MATCH(Product_Group,EI,0))),Sales)

But the key is how you're determining the range in the first instance (in the above case EI)

 

[Fujirich]

If I understand what you asking, I think I've obscured things a bit with that VLOOKUP.

Think of it like this -

--(Product_Group=EI)

Where EI is a named range and contains the product group names of EI DDCP, EI Inkjet, EI Inkjet WkFlw, EI News PlateSetr, EI Platesetter, EI Workflow.

Trying that doesn't seem to work, so I'm wondering if it's possible to use that "EI" named range somehow and expect it to return a sum for all of those product groups.

 

[DonkeyOte]

Did you try the sample formula ?

 

[Fujirich]

Wow, impressive lasw10 - your first solution worked great

Thanks again!!