Counting data from multiple columns

mbowen

New Member
Joined
Dec 13, 2007
Messages
5
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Iggy

New Member
Joined
Mar 22, 2011
Messages
26
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

New Member
Joined
Dec 13, 2007
Messages
5
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

New Member
Joined
Mar 22, 2011
Messages
26
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

New Member
Joined
Mar 22, 2011
Messages
26

ADVERTISEMENT

Okay forget that one....
 

Iggy

New Member
Joined
Mar 22, 2011
Messages
26
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,959
Messages
5,525,901
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top