+ Reply to Thread
Results 1 to 8 of 8

Count based on complex logic

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Angry 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?

  2. #2
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Count based on complex logic

    Please Login or Register  to view this content.
    Attach book for pivot table.
    Last edited by tlafferty; 08-23-2011 at 07:31 PM. Reason: added request for workbook
    If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
    Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...

    Thomas Lafferty
    Analyst/Programmer

  3. #3
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Count based on complex logic

    Spreadsheet Attached

  4. #4
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Count based on complex logic

    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

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Count based on complex logic

    Or

    =SUM(COUNTIFS(Data!S2:S81703, "<=61", Data!A2:A81703, {50,71,72,162}))
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Question Re: Count based on complex logic

    Quote Originally Posted by shg View Post
    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

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Count based on complex logic

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

  8. #8
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Count based on complex logic

    Quote Originally Posted by shg View Post
    =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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1