Results 1 to 7 of 7

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

Threaded View

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    LOS ANGELES
    MS-Off Ver
    OFFICE PRO 2021
    Posts
    56

    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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1