SUMIF formula for values in different rows

Thanks:  0
Likes:  0

# Thread: SUMIF formula for values in different rows

1. ## 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?

2. ## Re: SUMIF formula for values in different rows

So this doesn't work?

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

3. ## 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. ## 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. ## 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. ## Re: SUMIF formula for values in different rows

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

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

Originally Posted by vagabund
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. ## 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. ## Re: SUMIF formula for values in different rows

=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. ## 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. ## Re: SUMIF formula for values in different rows

Originally Posted by vagabund
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...

 2000 revenue 6000 revenue expense -3500 -3500 expense 4000 revenue

E2:

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

E3:

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

## User Tag List

#### Posting Permissions

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