# Most Efficient way of CountIfs combinations of multiple cells

1. ## 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. ## Re: Most Efficient way of CountIfs combinations of multiple cells

Hello,

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.``

3. ## Re: Most Efficient way of CountIfs combinations of multiple cells

Same macro, just a bit shorter :

``Please Login or Register  to view this content.``

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

#### Posting Permissions

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

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.05691 seconds
• Memory Usage 8,931KB
• Queries Executed 16 (?)
Template Usage (34):
• (2)bbcode_code
• (1)footer
• (1)forumrules
• (1)gobutton
• (3)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (3)postbit_legacy
• (3)postbit_onlinestatus
• (3)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (48):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete