Counting data from multiple columns

[mbowen]

I have a spreadsheet that has the following columns:

Zip Code (each row unique)
Store
Radio Market 1
Radio Market 2
Radio Market 3
Radio Market 4
Radio Market 5

Because a given location can be covered by multiple radio markets (for instance, southern Massachusetts can be covered by Springfield, MA and Hartford, CT), a given market can be in any one of the five columns. What I need is to get a count of individual stores by market, even though markets can be in any one of the five columns.

If there's a way that this can be done using a form with input fields (or drop-down lists) that would be even better, as I can palm it off on the sales drones to do it themselves.

Thanks in advance

 

[Iggy]

Is the Radio Market 1-5 Columns a True/False type of field?
As in :
Zip | Store | RM1 | RM2 |RM3|RM4|RM5|
07081| ???? | Y | Y | N |N |N |

And you want to know that Springfield is in 2 Radio Markets?
Do I have that right?

 

[mbowen]

No, it's like this:


Zip Store RM1 RM2 RM3 RM4 RM5
01001 Target Hartford Springfield
01501 Target Springfield
66001 Target Chicago
85710 Walmart Tucson Yuma Tombstone
95533 Target LA San Diego


And what they want to do is count how many Targets are in the Springfield market, no matter which column it shows up in. Then they want to know how many Walmarts are in Chicago, etc., etc.

 

[Iggy]

If I have that Right; You could change your Y and N to 1 and 0 then do a total Column and Sum it all in column H.
Then just do a VLookup of the Zip or Store and grab the Value in H for that row.

Of course if I am wrong then we can start over...

 

[Iggy]

Okay forget that one....

 

[Iggy]

Sounds like it would be better suited for a database then a spreadsheet.
Forms and such would be easier too.....IMO...

Let me think on it or see if anyone else has any ideas about it.

 

[mbowen]

Yeah, I know...they changed specs on me at the last second.