Count based on complex logic

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:

=COUNTIFS(Data!S2:S81704,"<=61",Data!A2:A81704,"162")

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?

Spreadsheet Attached

Well, it was supposed to be, but I got a security error from the server. Instead, I've posted it here:

http://dl.dropbox.com/u/3167423/Cens...ty%202009.xlsx

Originally Posted by shg

Or

=SUM(COUNTIFS(Data!S2:S81703, "<=61", Data!A2:A81703, {50,71,72,162}))

That's perfect -- thank you. One last question though. If I wanted to do exactly this, but change the <=61 part of it to a range of acceptable values, how would I do that? So for example, >=51, but <=100.

Thanks for all the help everyone!

dt

Originally Posted by shg

=SUM(COUNTIFS(Data!S2:S81703, ">=51", Data!S2:S81703, "<=100", Data!A2:A81703, {50,71,72,162}))

Awesome; thank you so much for the help. As usual, I was over-complicating things trying to figure it out!

I'd love any guidance folks can offer on how, step-by-step, I'd had set this up with a PivotTable, but this certainly answers what I need immediately.

Thanks again.


dt