Forum: General / Homework Help

Homework Help
Struggling with Excel formula, 48 hours
By Panda_Bear Comments: 410, member since Tue Feb 02, 2010
On Wed Oct 30, 2013 10:29 PM

I need help with finding and using the right formula in Microsoft excel to find out if data meets 2 criteria. It is for a 4 th year university geography of food course. My background is in geographic information systems and geography. I am working with about 400 records in my data table. I am looking for a formula that check to see if a record is with in a specific city and meets my type requirement. I have 15 possible types that it could be. I have my possible types across the top / x-axis and my cities on my vertical / y-axis. What formula would I use and how do I apply it to get the results I need to do my analysis?

Thanks for your help.

4 Replies to Struggling with Excel formula, 48 hours

re: Struggling with Excel formula, 48 hours
By panicmember has saluted, click to view salute photosPremium member Comments: 11592, member since Thu Dec 16, 2004
On Wed Oct 30, 2013 11:27 PM
If I understand you correctly, your table is set up inefficiently. You shouldn't use a separate column for each possible type. You could have one column for city and a second column for type. And in the Type column, you could have a comma-separated list of types for each city. Then you can use a substring search or an auto-filter to determine if each city is associated with a particular type.

In any case, it sounds like what you want is a filtered list and not a formula. Although it would really help if you could post the spreadsheet.
re: Struggling with Excel formula, 48 hours
By YumYumDoughnutPremium member Comments: 8688, member since Sat Jul 10, 2004
On Wed Oct 30, 2013 11:46 PM
Take a screenshot of the spreadsheet and post it. I ended up getting a minor in Decision Sciences, and this kind of stuff is the things I worked with.
re: Struggling with Excel formula, 48 hours
By Panda_Bear Comments: 410, member since Tue Feb 02, 2010
On Thu Oct 31, 2013 12:20 AM
I can't add a scree shot to DDN my computer won't let me. My columns are:

Business Name , Address, City, Type, Organic , empty, Name of CITY, Total Vendors, Dairy, Meat, Produce, Baked Goods, Prepared Product, Wine, Beer, Assorted Beverages, Farmers Market, Store, Sweets, Mixed, Overall total, Percent of total.

then each business/ vendor goes in a list down the side to the left of the blank column and cities are listed to the right of the blank column. I then am using " count if" to count the number of vendors in each city. I would like to get the number of each type of vendor in each city under their heading .
re: Struggling with Excel formula, 48 hours
By panicmember has saluted, click to view salute photosPremium member Comments: 11592, member since Thu Dec 16, 2004
On Thu Oct 31, 2013 12:56 AM
Ah. So you have TWO tables. One for vendors, and a summary table for cities. And the problem is that you can't use multiple criteria for the countif function. Now I'm up to speed.

Instead of countif, use sumproduct. It should look something like this =SUMPRODUCT((A1:A400="London")*(D1:D400="Dairy"))

ReplySendWatch