+ Reply to Thread
Results 1 to 3 of 3

Most Efficient way of CountIfs combinations of multiple cells

  1. #1
    Registered User
    Join Date
    04-04-2015
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    88

    Most Efficient way of CountIfs combinations of multiple cells

    I have created a sheet that somewhat shows what I'm working with.
    The file shows only top 9 rows of 20k rows of real file. So you know it is a pretty large sheet
    The format is similar and also if you look at it by hiding gridlines, it will be easier to see the borders defining each sections.

    There are multiple sections in each row.
    A section contains the columns (A-F) then next section starts (G-L), then so on.
    First column in section "abc" doesn't really matter what it is but it defines start of new section.
    Second column in section is "+" which I use to find out if whether to evaluate the following or not. (more on what to evaluate - which is what I need help with)
    Third column to six column has either blank, "a", "b", or "c".

    <--I need help with this-->

    For all the sections with "+" in second column,
    I need to count how many combinations of:
    "a" & "" & "" & ""
    "a" & "" & "" & "a"
    "a" & "" & "" & "b"
    "a" & "" & "" & "c"
    "a" & "" & "a" & "a"
    "a" & "" & "a" & "b"
    occurs

    I'm tried countifs but the file becomes very slow to open and save and calculate.
    So I'm looking for a way of doing so without formula. Possibly with vba array.

    testsheet.xlsb

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Most Efficient way of CountIfs combinations of multiple cells

    Hello,

    Add a sheet2 in your workbook and try this code.
    This will list all combinations and the how many occurences of each.
    From there you can filter and keep only those you want.

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Most Efficient way of CountIfs combinations of multiple cells

    Same macro, just a bit shorter :

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. COUNTIFS, multiple criteria and not including blank cells
    By qhoney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 05:14 PM
  2. more efficient way to do for-loop using application.worksheetfunction.countifs
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 09-08-2013, 10:36 PM
  3. [SOLVED] Most efficient way of comparing multiple cells in multiple columns.
    By soxcrates in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2013, 04:12 PM
  4. [SOLVED] If statement to check multiple cells for value combinations
    By Joe.lel in forum Excel General
    Replies: 3
    Last Post: 07-09-2012, 10:47 AM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 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