I'm working with a spreadsheet of all the US Census Bureau population data. The data worksheet breaks it all down into rows for every jurisdiction of any kind (city, county, state, and so forth) in the country, and has columns for the jurisdiction type (column A), name (F), and population in 2009 (S).
I'm doing a summary worksheet that outlines some of the more meaningful statistics for my client. In particular, I need to show a total count of jurisdictions -- of certain kinds only -- with a population meeting a certain criterion.
So for example, I want to show a total of count of all rows where jurisdiction = xxx OR yyy OR zzz; AND population <= 500.
I have no doubt this is probably infinitely easier to do with a PivotTable, but for the life of me I can't figure out how to do PivotTables for this, so I'm trying to use formulas instead. Works fine until I try conditional logic on the jurisdiction type.
The formula I have so far is:
So, if I wanted it to test for 162 OR 71 OR 72 OR 50, how would I do this?
That would be my first priority question. My second would be, step-by-step, how in the heck to create this as a PivotTable, but since time is of the essence, I really need the formulaic version nailed first.
Can anyone help here?