Excel - exclude data from based on another cell

Lee Jefferies 20 Reputation points
2026-06-13T15:07:01.58+00:00

I have the following formula pulling data:

=SUMIF(Fixtures!$C$257:$C$500,$B38,Fixtures!$F$257:$F$500)

Is it possible to add to this formula a way to stop it using the data in the fields if another cell in the origin “Fixtures” tab shows information eg. Yes.

In Fixtures!B2000:B2104 l want to show “Yes” to exclude the data in column F from being used. If the B column is blank the data in E would be used.

Microsoft 365 and Office | Excel | For home | Windows
0 comments
Answer accepted by question author

AI answer

2026-06-13T15:07:14.79+00:00

Use a SUMIFS formula so the sum is based on multiple conditions, including excluding rows where column B has "Yes".

Assuming:

  • Criteria in Fixtures!$C$257:$C$500
  • Values to sum in Fixtures!$F$257:$F$500
  • Exclusion flag ("Yes") in Fixtures!$B$257:$B$500
  • Criteria in B38

Use:

=SUMIFS(
    Fixtures!$F$257:$F$500,   
    Fixtures!$C$257:$C$500, $B38,   
    Fixtures!$B$257:$B$500, "<>Yes"  
)

Explanation:

  • Fixtures!$C$257:$C$500, $B38 keeps the original condition.
  • Fixtures!$B$257:$B$500, "<>Yes" tells Excel to sum only rows where column B is not "Yes".
  • If column B is blank, it is not equal to "Yes", so those rows are included.

If the "Yes" flags are in a different range (for example Fixtures!$B$2000:$B$2104), adjust the third range accordingly, but keep the ranges the same size and shape as the sum range to avoid performance issues.

References:

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

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Kai-H 27,310 Reputation points Microsoft External Staff Moderator
    2026-06-14T08:20:30.0266667+00:00

    Hi, Lee Jefferies

    Yes, you can do it, but the important part is that the “Yes/blank” range must line up with the rows you are summing. So if your original formula is checking rows 257 to 500, the exclude column also needs to be rows 257 to 500, not B2000:B2104. Otherwise Excel will not know which “Yes” belongs to which row.

    One alternative is to use SUMPRODUCT, which is useful when you want to include/exclude rows using more than one condition:

    =SUMPRODUCT((Fixtures!$C$257:$C$500=$B38)*(Fixtures!$B$257:$B$500<>"Yes")*Fixtures!$F$257:$F$500)

    This means: only add column F where column C matches B38, and column B is not Yes.

    If the values you actually want to add are in column E when column B is blank, then use this instead:

    =SUMPRODUCT((Fixtures!$C$257:$C$500=$B38)*(Fixtures!$B$257:$B$500="")*Fixtures!$E$257:$E$500)

    It is recommended that you keep all three ranges the same start and end rows, otherwise you may get wrong results or a formula error. Its array ranges need the same dimensions, and that TRUE/FALSE checks can be used as include/exclude conditions in the calculation.

    Thank you for your patience in reading, I hope this information has been helpful to you. 

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    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.