+ Reply to Thread
Results 1 to 3 of 3

Subtracting from the SUMIF with the difference from the SUMIF range

  1. #1
    Registered User
    Join Date
    09-04-2014
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    1

    Subtracting from the SUMIF with the difference from the SUMIF range

    My contract works on bankable time and I would like to be able to how much time I have rather than have to add it up.

    The number of hours is in cell E.

    Currently I have the formula =SUMIF(E:E,">7"), but I want to have it calculate so the sums of the time over 7 hrs shows up, not the overall sum of hours worked.

    So if I worked 8 hours for 3 days, I want to be able to display 3, not 24.
    Last edited by iamblue91; 09-04-2014 at 06:07 PM.

  2. #2
    Registered User
    Join Date
    04-04-2013
    Location
    Iron Mountain, MI
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Subtracting from the SUMIF with the difference from the SUMIF range

    Try this (changing your row reference as needed). I did a sample and it seemed to work.

    =SUMIF(E1:E5,">7",E1:E5)

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

    Re: Subtracting from the SUMIF with the difference from the SUMIF range

    Maybe something like this...

    Data Range
    E
    F
    G
    1
    Hours
    Hours >7
    2
    8
    8
    3
    8
    4
    8
    5
    4
    6
    7
    7
    12
    8
    ------
    ------
    ------


    This formula entered in G2:

    =SUMPRODUCT(--(E2:E7>7),E2:E7-7)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Shorten SUMIF + SUMIF + SUMIF each with same reference range but different criteria???
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2014, 09:25 PM
  2. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 AM
  3. How to SUMIF on a difference?
    By HooGolfer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2009, 11:46 PM
  4. sumif function with a difference
    By ozcank in forum Excel Formulas & Functions
    Replies: 58
    Last Post: 09-06-2005, 03:05 PM
  5. Replies: 2
    Last Post: 01-11-2005, 07:06 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