+ Reply to Thread
Results 1 to 4 of 4

Using SUMIFS (sum_range) to return total difference

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    uk
    MS-Off Ver
    2012
    Posts
    1

    Using SUMIFS (sum_range) to return total difference

    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?

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Using SUMIFS (sum_range) to return total difference

    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.

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using SUMIFS (sum_range) to return total difference

    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.

  4. #4
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Using SUMIFS (sum_range) to return total difference

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Sumifs for Dates. Sum total for each month
    By Xx7 in forum Excel General
    Replies: 3
    Last Post: 02-20-2014, 10:23 PM
  2. sumifs when criteria is met...sum up to total
    By mrjackt in forum Excel General
    Replies: 1
    Last Post: 01-09-2014, 12:19 PM
  3. [SOLVED] Using Sumifs (Or other Total Functions)
    By dgamboa9999 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2013, 03:14 AM
  4. difference between SUMPRODUCT versus SUMIFS
    By pl123zorro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2013, 12:46 PM
  5. SUMIFS Function: how to make the "sum_range" argument as a non-fixed variable
    By brunocinelli in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2013, 12:55 PM
  6. [SOLVED] Sumifs formula, how to use named range for Sum_Range
    By Kuehl5000 in forum Excel General
    Replies: 2
    Last Post: 02-26-2013, 11:18 AM
  7. [SOLVED] SUMIFS to total product A and B
    By Subject_Name_Here in forum Excel General
    Replies: 5
    Last Post: 05-26-2012, 11:56 AM
  8. Difference between Sumifs and Countifs in a chart
    By Befuddled in forum Excel General
    Replies: 5
    Last Post: 05-18-2010, 10:45 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1