hi
Is it possible to include a basic arithmetic calculation in a SUMIFS formula, as in =SUMIFS(A-B, range, conditions) etc? What I actually want to return is the difference between the total calculated across two columns, rather than the sum total
Example that fails: =SUMIFS(MySheet!D:D-MySheet!B:B, etc etc
I find that unless I just provide sum_range it throws an error
Is there another formula I should use?
You may want to create a helper column that you can hide which does the "MySheet!D:D - MySheet!B:B" first. Otherwise you may have to use an array formula which could be quite resource heavy if you are using a lot of rows.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
You can do this with the SUMPRODUCT function.
=SUMPRODUCT(--(A2:A10="X"),B2:B10-C2:C10)
You should avoid using entire columns as range references in array formulas and the SUMPRODUCT function. Use smaller specific ranges.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
You could also separate it into two parts within the same cell. You could do the first sumifs on MySheet!D:D and then - (Minus) a second sumifs on MySheet!B:B.
=SUMIFS(MySheet!D:D,etc, etc)-SUMIFS(MySheet!B:B,etc, etc)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks