Excel - exclude data from based on another cell

Question

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.

Answer 1

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.

Answer 2

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:

• Sum values based on multiple conditions
• SUMIF function
• Recalculation of a worksheet that contains a SUMIF formula takes longer than expected to finish