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