Count number of equal values in two rows in Excel

0 votes

My spreadsheet:

A, B, C, D, 10
A, B, X, Y, 10
A, B, C, D, 20

The first sentence serves as a model. Actual data are in the later lines. On each line, I want to count how many correct values there are. The correct value is one that corresponds to the value in the template line. Hence, the second line should be three, and the third line should be four.

I tried using COUNTIF, but I couldn't come up with an expression for the condition. Since SUMPRODUCT counts all pairs of equivalent values without regard to location, it also doesn't appear to be helpful in this situation.

Mar 21, 2023 in Others by narikkadan
• 63,600 points
591 views

1 answer to this question.

0 votes

 Use this formula:

=SUM(--(A2:E2=$A$1:$E$1))
answered Mar 21, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

Find occurrence count of a number in Excel

First split those integer values into their ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 63,600 points
982 views
0 votes
1 answer

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,600 points
826 views
0 votes
1 answer

Count unique matches between two rows in Excel

The formula should be (in Excel 365) =SUM(--(COUNTIF(Draws!$B$2:$G$18,B2:K2)>0)) so ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,600 points
569 views
0 votes
1 answer

Spell number in excel 2013 using 2 rows

Hello, there are a few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
601 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,690 points
4,155 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,690 points
2,022 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,690 points
999 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
3,737 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,000 points
569 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,000 points
1,324 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