excel - How to count each cells in a range that match the content of a different cell - Stack Overflow

I am trying to use COUNTIF in Excel to find how many items in a range match the contents of a separate cell. I'm using a spreadsheet as a way to score correct answers to an Oscar Predication Quiz (a one-word, non-numeric answer). Each column contains an individual's answers to a given category (Column B is one person's answers, Column C represents another participant's answers, etc.). The comparison column (Column J in this case) contains the correct answers. I want to create a formula that will count the total correct predictions each person got.

I started with:

=COUNTIF(B2;J2)

This will count if one cell matches. However, I’m trying to create a formula that will count each item if they match the other list.

I tried:

=COUNTIF(B2:B24;J2:J24)

But this didn’t work as it only counted if the entire list in Column B was identical to Column J (only gives 0 or 1). I want to count if each individual cell matches with the same row in Column J.

For example, if B2 and B3 matches both J2 and J3 respectively, but B4 doesn’t match J4, then this would count as 2.

I am trying to use COUNTIF in Excel to find how many items in a range match the contents of a separate cell. I'm using a spreadsheet as a way to score correct answers to an Oscar Predication Quiz (a one-word, non-numeric answer). Each column contains an individual's answers to a given category (Column B is one person's answers, Column C represents another participant's answers, etc.). The comparison column (Column J in this case) contains the correct answers. I want to create a formula that will count the total correct predictions each person got.

I started with:

=COUNTIF(B2;J2)

This will count if one cell matches. However, I’m trying to create a formula that will count each item if they match the other list.

I tried:

=COUNTIF(B2:B24;J2:J24)

But this didn’t work as it only counted if the entire list in Column B was identical to Column J (only gives 0 or 1). I want to count if each individual cell matches with the same row in Column J.

For example, if B2 and B3 matches both J2 and J3 respectively, but B4 doesn’t match J4, then this would count as 2.

• excel
• excel-formula
• countif

Share Improve this question Follow edited Feb 4 at 20:39 Mark S. 2,81911 gold badge99 silver badges2828 bronze badges asked Feb 4 at 12:51 NicoleNicole 122 bronze badges 4

• 2 edit in a data sample, using Markdown table for easy copy and pasting of the data. Also, since you didn't specify your excel version, can we assume it's 365? – Excellor Commented Feb 4 at 12:56
• 3 You can try this =SUM(--(B2:B24=J2:J24)) – Black cat Commented Feb 4 at 13:04
• I used Excel 365, but have switched to Google Sheets. Would formulas work the same way? – Nicole Commented Feb 5 at 13:07
• I tried that formula, but it returns as #VALUE! – Nicole Commented Feb 5 at 13:08

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

There are many ways to do this. Essentially, you are comparing two arrays. In Excel 365, you can simply enter the formulas as normal. In earlier versions of Excel, after entering the formula, press Ctrl+Shift+Enter (instead of just Enter) to confirm it as an array formula.

=SUMPRODUCT(--(A2:A5=B2:B5))
=SUM(IF(A2:A5=B2:B5, 1, 0))
=SUMPRODUCT(--(EXACT(A2:A5, B2:B5)))

In Google Sheets, in cell B26 enter

=SUM(ARRAYFORMULA(B2:B24 = J2:J24) * 1)

Note: SUMPRODUCT(COUNTIF(B2:B24,J2:J24)) does not work correctly.