How to get ARRAY formula to work over two variables from two tables on separate worksheets

[carleaston]

I am having a difficult time trying to avoid a whole bunch of nested IF statements, when I know some form of an Array function would be better and easier.
Simplifying my example..... let's say that I have seven stores that I am selling/shipping to.
And lets say that I have five (5) models of stereo receivers that I am selling to them.
Each of the models of stereo receivers has different features, such as AM/FM, XM, LAN connection and rack mount ability.
The features of each model of stereo receiver is on worksheet tab MODELS and is:
Model_lookup_1.JPG
The features of each model are indicated by a "x", and are blank if that feature is not provided on that model.

Then the reference table for the Stores and their ordering is on a worksheet tab labeled STORES:
So this tells me how many of what model receiver each store has ordered.
Model_lookup_2.JPG

The first thing I need to know is how many XM antennas to ship to each store.
So let's start easy enough with just Store #1.
I need to look at sheet STORES, B2:F2 to see what quantity of what models that Store #1 ordered.
And I need to reference back to sheet MODELS for each stereo receiver model they have ordered to see if it is equipped with an XM tuner before I can count the number of antennas needed.

So I start with the first receiver model 1000 in Column B:

=IF(VLOOKUP(STORES!B1,MODELS!A1:E6,3,FALSE)=1,IF(STORES!B2="",0,STORES!B2),0)

So I look to see if the Model Number of cell B1 has a check mark in column 3 to indicate that it has XM, if it does, then I check to see if they ordered any.
If they didn't I count zero, but if they did I count that total number.
This works fine and gives me the number of XM antennas for the first column.

What I can't figure out is how to ARRAY across the entire Row of Store #1, while individually checking back for each model number (i.e each column) if that specific model has an XM radio.
So I end up with a bunch of consecutive IF statements, which is tedious and UGLY and time consuming:
Here is just adding in the second column:

=IF(VLOOKUP(STORES!B1,MODELS!A1:E6,3,FALSE)=1,IF(STORES!B2="",0,STORES!B2),0)+IF(VLOOKUP(STORES!C1,MODELS!A1:E6,3,FALSE)=1,IF(STORES!C2="",0,STORES!C2),0)....+IF(for D2)....+IF(for E2)...+IF(for F2)

Gruesome!
I had hoped there was some way of making an Array something along the lines of:

{=IF(VLOOKUP(STORES!B1:F1,MODELS!A1:E6,3,FALSE)=1,IF(STORES!B2:F2="",0,STORES!B2),0)}

But that doesn't work.
I've tried INDEX and MATCH, but can't get that to work either.
So I'm totally at a loss.
Here was my closest attempt to make that work:

{=INDEX(MODELS!MODELS!A1:E6,MATCH(STORES!B2,MODELS!A1:E6,0))}

Then the second part of this is to count all of the total number of XM antennas I am going to need.
So that takes the Array across the entire table of STORES.
So one store is Array B2:F2.
And all stores is Array B2:F6.

Thank you for the time to at least read this, it's driving me crazy!!!!
It's got to be relatively straightforward, I just can't seem to figure it out.

............ Carleaston

[carleaston]

I am not sure if you can view the tables, so I am attaching them here as attachments.

.............carleaston

[carleaston]

Here is the Models table.


..........carleaston

[carleaston]

hmmmmmmmmmm.......... for some reason my MODELS table isn't showing up.
Let's try uploading it again.

...............carleaston