# Excel Sumifs help

Copper Contributor

# Excel Sumifs help

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.

I.e

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

Sumifs('mysummary'!F:F,'goals'!D:D-M5,'goals'!F5,'goals'!E:E,'goals'!G5)

9 Replies

# Re: Excel Sumifs help

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

# Re: Excel Sumifs help

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

# Re: Excel Sumifs help

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)

# Re: Excel Sumifs help

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.

# Re: Excel Sumifs help

This sadly won't work because it's not an average but thank you

# Re: Excel Sumifs help

I was trying to extract this data from a table with filters to numbers to help me process the data collectively into a table

# Re: Excel Sumifs help

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.

# Re: Excel Sumifs help

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

# Re: Excel Sumifs help

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