Embed a sum formula into a if formula

Ronnie Jordan 0 Reputation points
2026-09-11T19:42:13.56+00:00

I am trying to pose a question, If B5 is not empty, count the number of correct cells in B5 thru Q5 compared to B4 thru Q4 otherwise do not count.

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

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-09-11T19:42:32.65+00:00

    Use an IF test around a counting formula.

    If the goal is to count how many cells in B5:Q5 match the corresponding cells in B4:Q4, but return nothing when B5 is empty, use:

    =IF(B5="","",SUM(IF(B5:Q5=B4:Q4,1,0)))

    How it works:

    • IF(B5="","",...) checks whether B5 is empty.
    • If B5 is empty, the formula returns a blank.
    • If B5 is not empty, SUM(IF(B5:Q5=B4:Q4,1,0)) counts the matching cells across the two ranges.

    Important:

    • This is an array formula in the documented SUM + IF pattern.
    • Enter it by pressing Ctrl+Shift+Enter instead of just Enter. In Excel, that makes the formula evaluate the full range comparison.

    If only a single condition is needed, COUNTIF is also available for counting cells that meet a criterion, but for comparing one range directly to another range, the SUM(IF(...)) pattern fits this case.

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

    Was this answer helpful?

  2. Marcin Policht 106.8K Reputation points MVP Volunteer Moderator
    2026-09-11T20:19:04.24+00:00

    Use this formula:

    =IF(B5<>"",SUMPRODUCT(--(B5:Q5=B4:Q4)),0)

    It first checks whether B5 is not empty. If B5 contains a value, SUMPRODUCT compares each cell in B5:Q5 with the corresponding cell in B4:Q4 and counts the matches. If B5 is empty, it returns 0 and does not perform the count.

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

    Was this answer helpful?

    0 comments

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.