Excel Sumifs help

Copper Contributor

I need to use a sumifs formula to add up a group of numbers with 3 different criterias, and it also needs to subtract my varying goal for the day.



Sumifs('mysummary'!F:F,'goals'!D:D,'goals'!F5,'goals'!E:E,'goals'!G5) but each of those numbers need to subtract my goal set for the day in theory a 



9 Replies


Please explain how exactly you want to subtract the value of M5.

I need f1-m5 added to f2-m5...


You cannot subtract within the SUMIFS formula. Try this:


=SUMIFS('mysummary'!F:F, 'goals'!D:D, 'goals'!F5, 'goals'!E:E,'goals'!G5)-M5*COUNTIFS('goals'!D:D, 'goals'!F5, 'goals'!E:E, 'goals'!G5)

For consideration, create a helper table that modifies the Lookup value by your goal amount. So there is no formula for the criteria, but instead the actual criteria in the table is already modified by your goal.
This sadly won't work because it's not an average but thank you
I was trying to extract this data from a table with filters to numbers to help me process the data collectively into a table


Have you actually tried it? It does what you asked. If it is not what you want, please explain more clearly what you actually want to do.

Yes I have and it doesn't, I need f1-m5 to be added but not f2, then f3-m5, and f2-m6


How does M6 come into this? Please try to provide clear and detailed information.