+ Reply to Thread
Results 1 to 9 of 9

Help with SUMIF please

  1. #1
    Registered User
    Join Date
    12-12-2014
    Location
    Dorset, England
    MS-Off Ver
    Excel 2013
    Posts
    54

    Help with SUMIF please

    Hi Guys,

    I have attached a workbook to help make things easier.

    There are two small columns of data on the worksheet. I need a couple of functions which can reference one column of data, and then sum the adjacent cells (I have highlighted where I need the function to be placed.

    Thanks for your help guys!
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help with SUMIF please

    Try:

    E4:

    =MAX(IF($B$3:$B$17=MAX($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    E3:

    =SUM(OFFSET($C$3,,,E4,))

    E7:

    =ROWS($B$3:$B$17)-MAX(IF($B$3:$B$17=MAX($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))+1

    Confirmed with Ctrl-shift-enter

    E6:

    =SUM(OFFSET($C$17,,,-E7))
    Quang PT

  3. #3
    Registered User
    Join Date
    12-12-2014
    Location
    Dorset, England
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Help with SUMIF please

    Hi Bebo,

    Thanks very much, will try that now and get back ASAP

  4. #4
    Registered User
    Join Date
    12-12-2014
    Location
    Dorset, England
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Help with SUMIF please

    Quote Originally Posted by bebo021999 View Post
    Try:

    E4:

    =MAX(IF($B$3:$B$17=MAX($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    E3:

    =SUM(OFFSET($C$3,,,E4,))

    E7:

    =ROWS($B$3:$B$17)-MAX(IF($B$3:$B$17=MAX($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))+1

    Confirmed with Ctrl-shift-enter

    E6:

    =SUM(OFFSET($C$17,,,-E7))
    Hi Bebo,

    That works brilliantly, thanks very much!

    If I wanted to invert the functions and have, for example:

    1. Sum of volume from opening price to last min. value

    2. Sum of volume from last min. value to closing price

    do I just need to change "max" to "min"?

    Many thanks

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Help with SUMIF please

    Here is something to try:
    Find MAX of the Opening Price. I filled in the Opening Price column with Opening Price where there were blank cells in column A
    Enter in E1
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter with Ctrl + Shift + Enter

    Enter in E3
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter with Ctrl + Shift + Enter

    Enter in E4
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Enter with Ctrl + Shift + Enter

    Enter in E6
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter in E7
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help with SUMIF please

    Quote Originally Posted by zeegerman View Post
    Hi Bebo,

    That works brilliantly, thanks very much!

    If I wanted to invert the functions and have, for example:

    1. Sum of volume from opening price to last min. value

    2. Sum of volume from last min. value to closing price

    do I just need to change "max" to "min"?

    Many thanks
    Not really!
    For MIN, in E4:

    =MAX(IF($B$3:$B$17=MIN($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))

    E7:

    =ROWS($B$3:$B$17)-MAX(IF($B$3:$B$17=MIN($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))+1

  7. #7
    Registered User
    Join Date
    12-12-2014
    Location
    Dorset, England
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Help with SUMIF please

    Hi newdoverman,

    thanks very much, will try this in a bit and get back to you

  8. #8
    Registered User
    Join Date
    12-12-2014
    Location
    Dorset, England
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Help with SUMIF please

    Quote Originally Posted by bebo021999 View Post
    Not really!
    For MIN, in E4:

    =MAX(IF($B$3:$B$17=MIN($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))

    E7:

    =ROWS($B$3:$B$17)-MAX(IF($B$3:$B$17=MIN($B$3:$B$17),ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1,""))+1
    THANKS! I will try this in about 90 minutes and get back to you, but thanks very much for all your help so far!

  9. #9
    Registered User
    Join Date
    12-12-2014
    Location
    Dorset, England
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Help with SUMIF please

    Hi Bebo,

    Awesome, it works perfectly. Thanks ever so much for your help, and a big thanks to newdoverman.

+ 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. SumIf formula or Sumif with pivottable combined question
    By mcgleeuw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2015, 01:05 PM
  2. Replies: 4
    Last Post: 12-04-2014, 02:06 PM
  3. Subtracting from the SUMIF with the difference from the SUMIF range
    By iamblue91 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2014, 09:01 PM
  4. [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
  5. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  6. SUMIF Question: SUMIF not blank
    By nickyg in forum Excel General
    Replies: 5
    Last Post: 11-18-2009, 10:07 PM
  7. Nested SUMIF statement or multiple SUMIF's
    By Dan27 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-17-2009, 06:55 AM

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