Embed a if formula with a sum formula

Ronnie Jordan 0 Reputation points
2026-09-11T23:28:53.9833333+00:00

I want to embed a questions if B4 is empty return a 0 otherwise add all the correct cells in B6 thru Q6 compared to B4 thru Q4 otherwise show a 0

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

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-09-11T23:29:04.6833333+00:00

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

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

    How it works:

    • IF(B4="",0,...) returns 0 if B4 is empty.
    • IF(B6:Q6=B4:Q4,1,0) compares each cell in row 6 to the corresponding cell in row 4.
    • SUM(...) adds the matches, so each correct match contributes 1.

    If the goal is to return 0 unless B4 has a value, this formula matches that requirement.

    Note: formulas like this that use range comparisons with SUM(IF(...)) are array formulas in the referenced guidance.

    References:

    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-11T23:49:39.24+00:00

    Use this formula:

    =IF(B4="",0,SUMPRODUCT(--(B6:Q6=B4:Q4)))

    If B4 is empty, the formula returns 0. If B4 contains a value, it compares B6:Q6 against B4:Q4 and counts the number of cells that match.

    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.