# Count cells if any criteria are met, not multiple criteria

1. ## Count cells if any criteria are met, not multiple criteria

So I need to count the number of values in a column (G), only if the cells in columns A-F have a value in them. If any (or multiple) cell in A-F has a value in it, (G) should be counted. I have attached a dummy file with a sample of what I'm looking for with this.

I've tried COUNTIFS and SUMPRODUCT functions with no luck, I'm sure it's just that I'm not sure how to format it to make it work.

Helper columns are okay as long as I can hide them later I can make pretty much anything work with this workbook.

I am using one cell to total the count of these and the cell range in the actual sheet is: S2:X21, each cell will either be blank or have a 1 in them. The cells counted in the actual cell are AG2:AG21. This value will be calculated in cell AK9. This is a large work file so I would love to make it work as soon as possible.

Any help is appreciated. Thank you!

2. ## Re: Count cells if any criteria are met, not multiple criteria

Do you want to sum the values in G if the other cells are unpopulated, or simply count how many cells fit the criteria?

3. ## Re: Count cells if any criteria are met, not multiple criteria

Just a count of them. The values in G are useless, they are cross-reference numbers in the actual sheet.

Thanks.

4. ## Re: Count cells if any criteria are met, not multiple criteria

Not the most elegant, but if you are able to include a helper column, this should work for any text that populates a cell, and should be easy to implement in a bigger file. Hope this helps!

5. ## Re: Count cells if any criteria are met, not multiple criteria

Here's another method with a helper column

In H2 and filled down
=ISNUMBER(MATCH(1,A2:F2,0))

Then use
=SUMPRODUCT(--H2:H9,--(G2:G9<>""))

6. ## Re: Count cells if any criteria are met, not multiple criteria

Jonmo1 - Thank you! Your formulas worked great!

mcmahobt - Thank you for your help! Your way would have worked too, I just have OCD about errors and I'm too lazy to put it in an iferror function

Thank you both for the quick replies!

7. ## Re: Count cells if any criteria are met, not multiple criteria

Come to think about it, no need for testing for error (which is basically what ISNUMBER is doing)...

You can use this in H2 and filled down
=COUNT(A2:F2)
Then

=SUMPRODUCT(--(H2:H9>0),--(G2:G9,<>0))

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.06451 seconds
• Memory Usage 9,043KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (7)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)postbit_attachment
• (7)postbit_legacy
• (7)postbit_onlinestatus
• (7)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 (50):
• 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_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit
• tag_fetchbit_complete