Embed a If function with a sum function

Ronnie Jordan 0 Reputation points
2026-09-11T23:36:58.6433333+00:00

Use an IF formula to test whether B4 and B6 is blank, and if it is not blank, count the matches between B4:Q4 and B6:Q6 otherwise leave the cell blank

Microsoft 365 and Office | Excel | For home | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-09-11T23:37:10.7366667+00:00

    Use this formula:

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

    How it works:

    • IF(OR(B4="",B6=""),"",...) returns a blank if either B4 or B6 is blank.
    • SUM(IF(B4:Q4=B6:Q6,1,0)) compares each cell in B4:Q4 with the corresponding cell in B6:Q6 and counts the matches.

    If a legacy array formula is required, enter the formula and confirm it with Ctrl+Shift+Enter. In current Microsoft 365 versions, pressing Enter is sufficient for dynamic array behavior.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.