Results 1 to 3 of 3

WorksheetFunction Countifs with Multiple Criteria

Threaded View

  1. #1
    Registered User
    Join Date
    02-06-2014
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    12

    WorksheetFunction Countifs with Multiple Criteria

    Hello. I am currently working on a report that has a large amount of data on a summary sheet which includes 1200+ individuals and their sales results in 12 different categories. I have a front end summary sheet that makes it prettier and more user friendly with four different combo boxes with different criteria. The code was cumbersome, but it got the job done. Now they have asked me to add a fifth combo box so they can filter by an additional criteria. I used code to convert named ranges to a variable depending on what category the person fell into, but I need to do a count of how many individuals meet multiple criteria.

    If I only have one criteria, or if for example, somebody chooses Type A from the second combo box, FL from the third combo box, and a specific name from the first combo box, the countif formula I'm using works properly. Where I'm running into issues is when somebody wants to view the total for Types A and B, from all states, for all people.

    For the code below, PersonRange, TypeRange,StateRange are all defined ranges.

    Person, AgentType, and AgState are variables dependent on what is selected from combo boxes 1, 2 , and 3. The variables are fine if they select specific criteria, but if I try to define Person as "<>" so that it pulls back all people that are Type A and live in FL, Person returns as FALSE instead of "<>". Is it possible to count people where, for example, Type A is selected in All states?

    (The code is not in the attached file, it is just provided for an example of what my report looks like.) Sample Combobox Sheet.xlsx

    AgentCount = Application.WorksheetFunction.CountIfs(PersonRange, Person, TypeRange, AgentType, StateRange, AgState)
    Last edited by tlscowden; 04-17-2015 at 08:44 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  2. [SOLVED] Countifs with multiple criteria and one criteria has a list of names
    By Beefy1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2014, 05:25 PM
  3. worksheetfunction.countifs with loop from multiple worksheets
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2012, 04:55 PM
  4. worksheetfunction.countifs with loop
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2012, 01:26 PM
  5. How to make faster. Using Worksheetfunction.Countifs
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-14-2011, 05:51 AM

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