Sum data, excluding a certain text string, both now and in future reports

[mikeczyz]

I'm writing a formula to sum up revenue data with an additional criteria or two.

There are pay types which I want to exclude. Let's say that I want to exclude any paytype which includes the word "Apple" in the label. That is, I might want to include "Check," "Cash" and "Credit," but exclude "Apple Pie." As it stands, I can write a formula which hardcodes exclusion of "Apple Pie" dollars, but I need to pass this spreadsheet off to someone. At some point in the future, maybe we'll have "Apple Soda" show up in the pay type column and the spreadsheet won't know to exclude it because it was never hard coded into the original formula.

Any ideas on how you might deal with this?

Thanks!

for extra credit: One feature I wouldn't mind implementing is a simple checkbox or something so client can see what is or isn't being summed and check on and off additional types. This is probably the safest, best way of doing this.

 

[Beyond_avarice]

If you're using a "Sumif": either set your criteria to exactly what you want or don't "<>" want.

=SUMIFS(FNDWRR!$13:$O$13191, FNDWRR!$F13:$F$13191, "Check", FNDWRR!$F13:$F$13191,"Cash", FNDWRR!$F13:$F$13191, "Credit")

=SUMIFS(FNDWRR!$13:$O$13191, FNDWRR!$F13:$F$13191, "<>Apple*")

Your extra credit can be done with a pivot table and even better with slicers.

 

[mikeczyz]

Right, I can write the specific formula for today's data set, but tomorrow's might be different. I need to look into slicers. Thank you!