Count unique matches between two rows in Excel

0 votes

I've been attempting to fix this for a while, and I'm about there, but I just can't make it work.

We play a local lottery, and I have an Excel file with the results on one sheet and everyone's names and selected numbers on another.

I'm now attempting to develop a cell to track the number of numbers that each participant correctly predicted based on the numbers chosen. SUMPRODUCT(COUNTIF helped me partially, but if a number is drawn twice, it will also be counted twice in the final total.

Example page of people with their chosen numbers

As you can see, the number 11 should represent the total number of green cells (matches). It is now false because there are only seven green cells, not 11, in the first row.

In order to extract only the unique values using my formula, I, therefore, need some assistance. My current formula is:

=SUMPRODUCT(COUNTIF(B2:K2,Draws!$B$2:$G$18))
Mar 27, 2023 in Others by Kithuzzz
• 38,010 points
387 views

1 answer to this question.

0 votes

The formula should be (in Excel 365)

=SUM(--(COUNTIF(Draws!$B$2:$G$18,B2:K2)>0))

so it only counts matches in the Draws sheet once

Or

=SUMPRODUCT(--(COUNTIF(Draws!$B$2:$G$18,B2:K2)>0))

Earlier versions of Excel

enter image description here

The Draws sheet looks something like this:

enter image description here

answered Mar 27, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Count number of equal values in two rows in Excel

 Use this formula: =SUM(--(A2:E2=$A$1:$E$1)) READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
389 views
0 votes
1 answer

Excel - count days between two dates per year

Put this in C2 and copy over: =MIN(DATE(C1,12,31),$B$2)-MAX(DATE(C ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,700 points
290 views
0 votes
1 answer

Count Function for identifying the Unique values in Excel

If the 9th row is empty: =COUNTA(UNIQUE(TOCOL(F8:R10)))-1 If not ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
353 views
0 votes
1 answer

How to give space between two Textfield in Flutter?

Hi@akhtar, There is various way to give space ...READ MORE

answered Sep 8, 2020 in Others by MD
• 95,440 points
4,309 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,819 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,582 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
771 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,679 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,700 points
554 views
0 votes
1 answer

How can I count the rows with data in an Excel sheet?

With formulas, what you can do is: in ...READ MORE

answered Dec 27, 2022 in Others by narikkadan
• 63,700 points
396 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP