Page 1 of 2 12 LastLast
Results 1 to 10 of 16

SUMIF formula for values in different rows

This is a discussion on SUMIF formula for values in different rows within the Excel Questions forums, part of the Question Forums category; Hi guys, I thought that SUMIF FUNCTION should work for what I am trying to achive but it does not ...

  1. #1
    New Member
    Join Date
    May 2005
    Location
    Canada
    Posts
    19

    Default SUMIF formula for values in different rows

    Hi guys,
    I thought that SUMIF FUNCTION should work for what I am trying to achive but it does not look like.
    Here is the example: 1,2,3 etc. are rows and A, B,C - columns
    A B
    1 Gain (Loss) on Balance sheet reval, 91105 (2,035,611)
    2 Expense
    3
    4 NET Gain (Loss) on LTD 945,600
    5 including Hedge on $ million Revenue

    I need to write the formula in cell e.g. A8 that would look at my either "Expense" or "Revenue" and sum it if it is revenue or sumit if it is expense. I have the whole spreadsheet of rows and the label "revenue" or "expense" repeats almost always after 2 rows.
    Any way that I can do that?
    Thank you for your help.

  2. #2
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,939

    Default Re: SUMIF formula for values in different rows

    So this doesn't work?

    =SUMIF($A$1:$A$100, "Expense", $B$1:$B$100)

  3. #3
    New Member
    Join Date
    May 2005
    Location
    Canada
    Posts
    19

    Default Re: SUMIF formula for values in different rows

    No it does not as my condition is in the same column as my value that I need to do the sum.
    See below.
    Cell A2- 2000
    Cell A3 - "revenue"
    Cell A4 - empty
    Cell A5 - (3500)
    Cell A6 - "expense".

    My formula should be able to read through this column A and based on the condition whether it is revenue or expense sum up the values.
    Hope this makes it clearer.

  4. #4
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,939

    Default Re: SUMIF formula for values in different rows

    No, not really... are you saying that after each value, the cell immediately below indicates revenue/expense category? Or are you saying that these are headings and each only repeats once?

    If you can, post a larger sample with several of each revenue and expense.

  5. #5
    New Member
    Join Date
    May 2005
    Location
    Canada
    Posts
    19

    Default Re: SUMIF formula for values in different rows

    Yes,
    Below each cell with a value there is a cell that I have put formula in and that categorizes the above it cell as iether a revenue or expense.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,129

    Default Re: SUMIF formula for values in different rows

    =SUMIF(A3:A6,"revenue",A2:A5)

    =SUMIF(A3:A6,"expense",A2:A5)

    Quote Originally Posted by vagabund View Post
    No it does not as my condition is in the same column as my value that I need to do the sum.
    See below.
    Cell A2- 2000
    Cell A3 - "revenue"
    Cell A4 - empty
    Cell A5 - (3500)
    Cell A6 - "expense".

    My formula should be able to read through this column A and based on the condition whether it is revenue or expense sum up the values.
    Hope this makes it clearer.

  7. #7
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,939

    Default Re: SUMIF formula for values in different rows

    OK, that's going to be a bit more involved than a SUMIF.

    In my sample, I'm using the following data, in A2:A10:

    10
    revenue
    100
    revenue

    1000
    expense
    2
    expense

    In cell C2 (which doesn't really make a difference, that's just the one I picked), I have the following formula:

    Code:
    =SUM(IF(ISNUMBER($A$2:$A$10),$A$2:$A$10,0)*($A$3:$A$11="revenue"))
    You must press Ctrl+Shift+Enter, instead of just Enter, when confirming this formula.

    The result is 110. Notice the last reference is offset by one, so in this case A3:A11 because the data is in A2:A10. Adjust both to suit your needs, and make sure they're the same size (9 rows in my example).

    For expenses, the corresponding formula (D2 in my worksheet) is as follows:

    Code:
    =SUM(IF(ISNUMBER($A$2:$A$10),$A$2:$A$10,0)*($A$3:$A$11="expense"))
    The result is 1002.

    Does that work for you?

  8. #8
    Board Regular iliace's Avatar
    Join Date
    Jan 2008
    Location
    Maryland, USA
    Posts
    2,939

    Default Re: SUMIF formula for values in different rows

    Quote Originally Posted by Aladin Akyurek View Post
    =SUMIF(A3:A6,"revenue",A2:A5)

    =SUMIF(A3:A6,"expense",A2:A5)
    Oh... I guess SUMIF does work. I guess I'm just overly obsessed with arrays.

  9. #9
    New Member
    Join Date
    May 2005
    Location
    Canada
    Posts
    19

    Default Re: SUMIF formula for values in different rows

    Just SUMIF does not work as it has picked up only 1 value for me instead of two. You are right I do have some blank rows in between and that is why the formula does not recognize the values. So I will try with array and let you know.
    Thanks.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,129

    Default Re: SUMIF formula for values in different rows

    Quote Originally Posted by vagabund View Post
    Just SUMIF does not work as it has picked up only 1 value for me instead of two. You are right I do have some blank rows in between and that is why the formula does not recognize the values. So I will try with array and let you know.
    Thanks.
    Probably you didn't notice the pecularity in range specs...

    Data: A2:A9...

    2000revenue6000
    revenueexpense-3500
    -3500
    expense
    4000
    revenue


    E2:

    =SUMIF(A3:A9,D2,A2:A8)

    E3:

    =SUMIF(A3:A9,D3,A2:A8)

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com