How to calculate value based on certain condition and values from previous rows

[kxtg2431]

I want to make a worksheet which looks like this:
it has 4 columns : Department name, spending, Final Balance, description. Example:
I have two department : IT and Finance. Each is given annual budget where each department can spend. IT got $5000 and Finance got $4000, so the worksheet will look like this (the column order is dept name, spending, final balance, and additional notes column, each column value is quoted):

"IT" "5000" "5000" "beginning balance"
"Finance" "4000" "4000" "beginning balance"
"IT" "150" "4850" "stationery spending"
"Finance" "300" "3700" "overtime charge"
"IT" "200" "4650" "PC maintenance"
"Finance" "700" "3000" "stationery spending"

and so on. so everytime I add a new row, depending on the department, the value in "final balance" column will calculate automatically the final balance value of the department.
for example : I want to add the seventh row where the department is IT and spending value is 500, the final balance column will calculate automatically and produce 4150.
I prefer to re calculate from the top row (the beginning balance rows) everytime any row in the worksheet is inserted, deleted or modified, the final balance in all other rows will adjust accordingly (data integrity is maintained). how can I do this? Thanks a lot

[etaf]

you could use a SUMIF()
(SUMIF(A3:A7,A7,B3:B7))
and subtract from the starting balance
5000-(SUMIF(A3:A7,A7,B3:B7))
or the cell as an absolute reference
$B$1-(SUMIF(A3:A7,A7,B3:B7))

[kxtg2431]

I need excel to automatically detect the department name. so if in the newly added seventh row, I change the department name from IT to finance, the final balance column will adjust the value accordingly. thanks for the reply

[etaf]

I need excel to automatically detect the department name. so if in the newly added seventh row, I change the department name from IT to finance, the final balance column will adjust the value accordingly. thanks for the reply

that formula will - it will pick up what ever is entered into column A and use that as the criteria
the only issue is the starting balance - you can enter that manually in an if

=IF( A7="IT", $B$1-(SUMIF(A3:A7,A7,B3:B7)),$B$2-(SUMIF(A3:A7,A7,B3:B7)))

this will work for the example

OR to keep the cell blank until an entry in A?
=IF( A7="","",IF(A7="IT", $B$1-(SUMIF(A3:A7,A7,B3:B7)),$B$2-(SUMIF(A3:A7,A7,B3:B7))))

[kxtg2431]

actually what i have in mind is more like this : everytime I add new row and change the value of the department name or the spending value, excel will automatically iterate from the first row to the recently inserted row. and during the iteration, excel will check if the current iterated row's department name is the same as the recently inserted row's department name. if so, excel will calculate the value of final balance, otherwise, the row is ignored. excel will keep doing this until the recently inserted row has been reached in the iteration. is this possible? this way, we don't need lengthy IFS since there are almost 50 departments and sub departments. thanks

[etaf]

that is what it does , it looks at the entry in cell A? and then sums all the values from row 3 to the row you are entering data

the reason for the IFs was to get the opening balance , which would be different for each department - so I guess this is the bit that needs to be automated to exclude the IFs

we maybe able to use a vlookup to get the first value of opening balance - will the opening balance always be on the first setof rows ?

or can the beginning balance be somewhere else ?
will it always have a spend figure of the opening balance as your example or zero
if zero we can probably lookup for opening beginning balance and the dept and then use that value
and then sum the spend - issue is I'm not sure how to do it with the example as your beginning balance has a spend of the beginning balance
OR

we start the formula after all the depts beginning balance are entered and then start the formula from that row and then no change needed- as in the example spreadsheet attached


"IT" "0" "5000" "beginning balance"
"Finance" "0" "4000" "beginning balance"
"IT" "150" "4850" "stationery spending"
"Finance" "300" "3700" "overtime charge"
"IT" "200" "4650" "PC maintenance"
"Finance" "700" "3000" "stationery spending"

as i have changed here

you could then use this formula after all the beginning balance are entered
=IF(A3="","",SUMPRODUCT(($A$1:A2=A3)*($D$1:D2="beginning balance")*($C$1:C2))-(SUMIF($A$3:A3,A3,$B$3:B3)))
the sumproduct will look for the beginning balance for the dept in the A cell and then subtract that from the sum of the spend for that dept

as i say the issue I have is where the beginning balance has the spend of the beginning balance and if they are all entered first - I'm sure another member may help with a better solution

see attached the highlighted area is where I added the formula

[kxtg2431]

^
^
yes, the beginning balance will always be placed on the first set of rows.
no, the row describing beginning balance doesn't always need to have value in the spending value. only the final balance value matters in these set of rows. if your solution needs the spending value to be zero in this set of rows, it's fine, as long as it can calculate the final balances of the next rows correctly.

I tried your solution, but when I tried to insert new dept namely accounting on third row with 7000 beginning balance and then add new row in the last position for accounting department, the final balance column was set to 4000.
thanks for the reply.

[etaf]

yes inserting the row is not moving the formula correctly
so the formula is not picking up the inserted row correctly - as i say not a great solution if you are going to be inserting rows for depts

if you know how many depts you have and set that up then you can adjust the formula to work from the first row of actual spend

=IF(A3="","",SUMPRODUCT(($A$1:A2=A3)*($D$1:D2="beginning balance")*($C$1:C2))-(SUMIF($A$3:A3,A3,$B$3:B3)))

therefor A3 and B3 would change to the cell for the first spend
and A, D and C the A2,D2,C2 to the cell before the row spend

assuming the spend starts in row 51
=IF(A51="","",SUMPRODUCT(($A$1:A50=A51)*($D$1:D50="beginning balance")*($C$1:C50))-(SUMIF($A$51:A51,A51,$B$51:B51)))
and then copy down

sorry not a great solution

[etaf]

i think this will work and cope with inserted rows

Put this into cell C2
=IF(A2="","",SUMPRODUCT(($A$1:A2=A2)*($D$1:D2="beginning balance")*($B$1:B2))-SUMPRODUCT(($A$1:A2=A2)*($D$1:D2<>"beginning balance")*($B$1:B2)))

and copy down
you need to put the beginning balance in cell B2 as you had it
and this should work to show beginning balance and spend and also you can insert a row and add a dept

see attached sheet - now you can insert a row anywhere and put beginning balance will take that away from any row for that dept that does not have "beginning balance"

the dept beginning balance row - must be before any spend for that dept - it will work out but will show -ve values until it finds the beginning balance

I'm sure they maybe an easier way to do this

see attached sheet updated with header row

[kxtg2431]

yes, this works. thank you very much for the tireless effort, etaf!!!
I'll reply here if I have other question related to this problem. Once again, thank you so much!!!