Embedding a if formular with a sum formular

Use an IF formula to return 0 when the key cells are empty, and otherwise count the matches across the two ranges.

If the goal is:

• if B4 and B6 are empty, return 0
• otherwise compare B6:Q6 to B4:Q4 and count the matching cells

use:

=IF(AND(B4="",B6=""),0,SUM(IF(B4:Q4=B6:Q6,1,0)))

How it works:

• AND(B4="",B6="") checks whether both cells are empty.
• If both are empty, the formula returns 0.
• IF(B4:Q4=B6:Q6,1,0) creates an array of 1s and 0s based on whether each pair of cells matches.
• SUM(...) adds those results to give the total number of matches.

If the requirement is to return 0 when either B4 or B6 is empty, use this instead:

=IF(OR(B4="",B6=""),0,SUM(IF(B4:Q4=B6:Q6,1,0)))

If Excel requires array entry for this formula, enter it with Ctrl+Shift+Enter.

• How to use a logical AND or OR in a SUM+IF statement in Excel
• Guidelines and examples of array formulas
• Use nested functions in an Excel formula
• EXCEL - IF formula based on cell value or entry - Microsoft Q&A