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. 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 in Others 60 views

## 1 answer to this question.

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 The Draws sheet looks something like this: • 63,040 points

## Count number of equal values in two rows in Excel

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

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

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

## How to give space between two Textfield in Flutter?

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

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

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

## Convert Rows to Columns with values in Excel using custom format

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

## IF - ELSE IF - ELSE Structure in Excel

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