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

1. ## 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.

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

=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. ## Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

Originally Posted by Bo_Ry

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

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

=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. ## 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. ## Re: Formula to count matches between multiple columns on different sheets w/ multiple crit

=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)),"")

7. ## 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!

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.07785 seconds
• Memory Usage 9,070KB
• Queries Executed 16 (?)
Template Usage (35):
• (2)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (7)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (3)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 (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_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_complete