+ Reply to Thread
Results 1 to 7 of 7

Count cells if any criteria are met, not multiple criteria

  1. #1
    Registered User
    Join Date
    03-27-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    3

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    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?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    03-27-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    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. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    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!
    Attached Files Attached Files

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #6
    Registered User
    Join Date
    03-27-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    3

    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. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

    Glad to help...

    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))

+ 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. Count statement wtih multiple criteria only showing results for one criteria
    By uhlabomber in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2013, 02:47 PM
  2. Replies: 5
    Last Post: 09-22-2009, 06:11 PM
  3. Count cells with multiple criteria
    By bmunoz64 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 04-06-2009, 07:10 AM
  4. Multiple count criteria in different cells
    By Jacquesvs in forum Excel General
    Replies: 2
    Last Post: 01-18-2007, 03:41 AM
  5. [SOLVED] count cells using multiple criteria
    By Alex68 in forum Excel General
    Replies: 4
    Last Post: 05-24-2005, 01:06 PM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.06451 seconds
  • Memory Usage 9,043KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (2)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
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
  • ./showthread.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_facebook.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_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • 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
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1