# Thread: Sumifs Question using Excel 2010

1. ## Sumifs Question using Excel 2010

I have a budget spreadsheet using sumifs but have not figured out how to exclude certain data to prevent duplicates in my sum. I have attached a sample spreadsheet that should give the basics of what I am trying to accomplish. I want to sum column M when column D is greater than 5* but I want to exclude 597. I tried using "<> 597" in my formula but that didn't work. Column B is department. Attached spreadsheet shows what is being calculated vs. what the answer should be.

Attachment: Budget with sumifs.xlsx

Example formula for summing Capital Outlay
=SUMIFS(\$M\$3:\$M\$26,\$B\$3:\$B\$26,"301",\$D\$3:\$D\$26,"=5 *",\$D\$3:\$D\$26, "<>597",\$F\$3:\$F\$26,"=6*")

Example formula for summing Debt Service
=SUMIFS(\$M\$3:\$M\$26,\$B\$3:\$B\$26,"301",\$D\$3:\$D\$26,"=5 *",\$D\$3:\$D\$26, "<>597",\$F\$3:\$F\$26,">=7*",\$F\$3:\$F\$26,"<=9*")

Note that both are including 597 even though I have told it to exclude 597.

I would also like to combine transfers out with agency transfers but haven't found a way to add items in column F that are in the range of 90-99 and have 597 without running into problems.

2. This also does not work.

=SUMIFS(\$M\$3:\$M\$26,\$B\$3:\$B\$26,"301",\$D\$3:\$D\$26,">5 01",\$D\$3:\$D\$26, "<597",\$F\$3:\$F\$26,"=6*")

3. DG,

=SUMIFS(\$M\$3:\$M\$26,\$D\$3:\$D26,"=5*")-SUMIFS(\$M\$3:\$M\$26,\$D\$3:\$D26,"=597")

Gives a result of 125,328 which is what selecting the correct cells in col M showes on the status bar.

4. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

DGStewart (2012-09-18)

5. You can also use something like:
Example formula for summing Capital Outlay
=SUMIFS(\$M\$3:\$M\$26,\$B\$3:\$B\$26,"301",\$D\$3:\$D\$26,"=5 *",\$D\$3:\$D\$26, "<>597*",\$F\$3:\$F\$26,"=6*")

Example formula for summing Debt Service
=SUMIFS(\$M\$3:\$M\$26,\$B\$3:\$B\$26,"301",\$D\$3:\$D\$26,"=5 *",\$D\$3:\$D\$26, "<>597*",\$F\$3:\$F\$26,">=7*",\$F\$3:\$F\$26,"<=9*")

The problem with your SUMIFS is that the TEXT that looks like numbers in your columns, is not being compared to the TEXT "597" but to the NUMBER 597. The Text values never equal the number value!. With the "5*", etc you explicitly make it a text comparison, so you need to make the "597" a text comparison as well. Adding an asterisk does this.

Steve

6. ## The Following User Says Thank You to sdckapr For This Useful Post:

DGStewart (2012-09-18)

7. I couldn't get this one to work. Missing argument. I was able to subtract sumifs as mentioned by RetiredGeek. Thanks to both of you. I do appreciate your help. New sumifs question on its way.

8. Sorry about that. Some of the post was truncated for some reason. I have fixed it...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•