+ Reply to Thread
Results 1 to 7 of 7

Formula to count matches between multiple columns on different sheets w/ multiple criteria

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    29

    Formula to count matches between multiple columns on different sheets w/ multiple criteria

    I normally will spend hours if necessary to find the formula to a problem. Until now I have eventually figured every one of them out except this time, i am stumped.
    I am putting together a spreadsheet for players in a contest and trying to work out a formula for consensus selections between the players. I have almost got the results I wanted using both SUMPRODUCT and COUNTIFS but with flaws in both.
    All the sheets are identical, column A is dates, column B is a selection, column O is a selection, column AA is the sum of winners in that row (result is always 1 if there is a selection and there can only be 1), column AB is same as AA just that its the losers.

    I am trying to figure out a formula that will compare a range in column A on Sheet1 to a range in column A on Sheet2 for matches if column A and B OR column A and O are the same and if the match was a win (by matching column AA) or loss (by matching column AB) showing as a W-L format. The following is the best I could come up with but it only compares column A and B (doesnt check A and O) and if column A and B are the same on row 16 on sheet1 and the same exists on sheet2 on row 15

    =SUMPRODUCT(--(A9:A32=Sheet2!A9:A32)*(Sheet1!B9:B32=Sheet2!B9:B32)*(Sheet1!AA9:AA32=1))&"-"&SUMPRODUCT(--(A9:A32=Sheet2!A9:A32)*(Sheet1!B9:B32=Sheet2!B9:B32)*(Sheet1!AB9:AB32=1))

    Sorry if it was confusing, I included a copy of the spreadsheet for clarity.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,050

    Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

    Please try at AE10

    =SUMPRODUCT(COUNTIFS(A9:A32,Sheet2!A9:A32,B9:B32,Sheet2!B9:B32),AA9:AA32)&"-"&SUMPRODUCT(COUNTIFS(A9:A32,Sheet2!A9:A32,B9:B32,Sheet2!B9:B32),AB9:AB32)

    I'm not sure on how you calculate the result for
    column A and B OR column A and O are the same
    Please provide more sample and the expected result.

    Maybe this for Win??
    =MAX(SUMPRODUCT(COUNTIFS(A9:A32,Sheet2!A9:A32,B9:B32,Sheet2!B9:B32),AA9:AA32),SUMPRODUCT(COUNTIFS(A9:A32,Sheet2!A9:A32,O9:O32,Sheet2!O9:O32),AA9:AA32))

  3. #3
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    29

    Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

    Quote Originally Posted by Bo_Ry View Post
    Please try at AE10

    =SUMPRODUCT(COUNTIFS(A9:A32,Sheet2!A9:A32,B9:B32,Sheet2!B9:B32),AA9:AA32)&"-"&SUMPRODUCT(COUNTIFS(A9:A32,Sheet2!A9:A32,B9:B32,Sheet2!B9:B32),AB9:AB32)
    That formula doesnt work either, the result is not being correctly determined.

    I can eliminate the need for column O to be checked. What this spreadsheet needs to do is to check each row in a range and count matches in the same range on a different sheet that will likely not be in the same rows.
    So the date in column A AND the corresponding 3 letter code in column B AND a "1" in column AA must be an identical match to the same in another sheet (possibly a different row) for it to be counted.
    So if the date of 1/2/19 matches in sheet1 and sheet2 anywhere in the range A9:A32, then column B and AA must be a match as well. The date in sheet1 may be located in A19 but in sheet2 it is located in A23. As long as the rest of the columns in that row are a match then it should be counted as a match.

    I have included another example with a new sheet added giving an example. Thanks for the help.
    Attached Files Attached Files

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,050

    Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

    Please try again

    =SUMPRODUCT(COUNTIFS(Sheet2!A9:A32,A9:A32,Sheet2!B9:B32,B9:B32),AA9:AA32)&"-"&SUMPRODUCT(COUNTIFS(Sheet2!A9:A32,A9:A32,Sheet2!B9:B32,B9:B32),AB9:AB32)

  5. #5
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    29

    Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

    Thats it except for 1 thing...

    Look at row 10 in NEWSheet1, there is a red "1" in E10 which is a match in Sheet2. If you delete the "1" in E10 and place a "1" in D10 then the count becomes 4-0 from 3-1 when it should be 3-0 since a match was just eliminated. It should not be counted as a match since one was a winner and one was a loser. That is what I meant by they also have to match the value in the AA or AB column as well. It should be the third criteria that they are equal.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,050

    Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

    Please try again

    =SUMPRODUCT(COUNTIFS(Sheet2!A9:A32,A9:A32,Sheet2!B9:B32,B9:B32,Sheet2!AA9:AA32,AA9:AA32),AA9:AA32)&"-"&SUMPRODUCT(COUNTIFS(Sheet2!A9:A32,A9:A32,Sheet2!B9:B32,B9:B32,Sheet2!AB9:AB32,AB9:AB32),AB9:AB32)

    AA9 can change to
    =IFERROR(1/(1/SUMIFS($D9:$Z9,$D$7:$Z$7,AA$8)),"")
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE 2016
    Posts
    29

    Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

    Wow, thats it! My brain hurts a little less when I look at that formula now.

    As for the replacement formula for AA9, it took me a second to get it but it also helped me to understand both the formulas a little better and how I may be able to use them.

    Thank you very much!

+ 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. [SOLVED] SUMPRODUCT of multiple columns with multiple criteria over multiple sheets
    By BellyGas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-16-2015, 08:27 AM
  2. [SOLVED] Need formula to count multiple criteria in 2 columns
    By tancho321 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2013, 04:53 PM
  3. Replies: 0
    Last Post: 01-11-2013, 02:39 AM
  4. Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  5. Replies: 2
    Last Post: 09-16-2011, 07:45 AM
  6. Count - multiple criteria, multiple sheets, and date range.
    By threecliffs in forum Excel General
    Replies: 6
    Last Post: 06-14-2011, 01:36 PM
  7. trying to count unique occurences w/in data that matches multiple criteria
    By broro183 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2006, 08:11 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07785 seconds
  • Memory Usage 9,070KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (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
  • (2)bbcode_quote
  • (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
  • (3)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)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 (49):
  • 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_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