So this doesn't work?
=SUMIF($A$1:$A$100, "Expense", $B$1:$B$100)
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 ...
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.
So this doesn't work?
=SUMIF($A$1:$A$100, "Expense", $B$1:$B$100)
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.
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.
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.
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:
You must press Ctrl+Shift+Enter, instead of just Enter, when confirming this formula.Code:=SUM(IF(ISNUMBER($A$2:$A$10),$A$2:$A$10,0)*($A$3:$A$11="revenue"))
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:
The result is 1002.Code:=SUM(IF(ISNUMBER($A$2:$A$10),$A$2:$A$10,0)*($A$3:$A$11="expense"))
Does that work for you?
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.
Like this thread? Share it with others