Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that will subtract the sum of one cell if the sum of another cell is greater than zero, and have it applied to cells across a document that is 30,000 entries and only in certain spots
Solved by F. J. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/07/2018 - 02:19
hi
User
25/07/2018 - 02:20
Hi
Excelchat Expert
25/07/2018 - 02:20
can you give an example
Excelchat Expert
25/07/2018 - 02:20
can you share your document?
User
25/07/2018 - 02:20
I can't share the document as it is confidential for the company, but I'll try and give an example
Excelchat Expert
25/07/2018 - 02:21
thanks
User
25/07/2018 - 02:21
okay we can use these numbers on the doc
User
25/07/2018 - 02:21
there are no formulas there at the moment since it's from a different program
Excelchat Expert
25/07/2018 - 02:22
ok
Excelchat Expert
25/07/2018 - 02:22
so what is needed?
Excelchat Expert
25/07/2018 - 02:22
and which column?
User
25/07/2018 - 02:22
so cell 29C, that is the total cost of labour, and 29F is the cost of material, but since the labour is booked in under both categories it ends up being added twice to the sum
Excelchat Expert
25/07/2018 - 02:23
oh okk
Excelchat Expert
25/07/2018 - 02:23
so if c29>0 you want to subtract it from f29?
User
25/07/2018 - 02:24
not the whole thing, since it has other costs included from elsewhere on the spreadsheet
Excelchat Expert
25/07/2018 - 02:24
hmm
Excelchat Expert
25/07/2018 - 02:25
so what would you need to subtract from f29 then?
User
25/07/2018 - 02:25
and as well, sometimes when these are updated the numbers across the board can't always be added since these numbers here are finished, while sometimes there is still current projects that aren't finished costs yet
Excelchat Expert
25/07/2018 - 02:25
ok
User
25/07/2018 - 02:26
f 29 would need C1-28 subtracted IF G and H cells 1-28 are >0
Excelchat Expert
25/07/2018 - 02:26
so how would you want labour added twice to the sum avoided?
Excelchat Expert
25/07/2018 - 02:26
ok got it
User
25/07/2018 - 02:26
since on my sheet the G and H columns are currents
Excelchat Expert
25/07/2018 - 02:27
how do you get f29 currently in your sheet? is there a formula
User
25/07/2018 - 02:28
I assume there is a formula unfortunately they didn't appear on Excel since it was dumped from an adobe program i believe
User
25/07/2018 - 02:28
but I don't know what they are
Excelchat Expert
25/07/2018 - 02:28
ohhh
User
25/07/2018 - 02:29
as far as i can tell, f29 is sum all F column and would be 1-28 C column
Excelchat Expert
25/07/2018 - 02:30
ok from this data sum of column C isnt adding to column F but I understanfd the formula your looking for give me 1 min
User
25/07/2018 - 02:31
sorry it's hard to explain, I don't even really know what's going on, my boss just sent me an email and briefly explained
Excelchat Expert
25/07/2018 - 02:31
:)
User
25/07/2018 - 02:31
okay thanks, see what you can come up with, if not no worries
Excelchat Expert
25/07/2018 - 02:31
ok H cells 1-28 you mean to say the sum of those cells right
User
25/07/2018 - 02:32
yea i just didn't copy as far as H from my spreadsheet
User
25/07/2018 - 02:33
I gotta head out right now, so I won't be able to answer any more questions, whatever you think might work send it to the chat and I'll try it out when I get back. Thanks for your help! don't wait around.
Excelchat Expert
25/07/2018 - 02:33
oh
Excelchat Expert
25/07/2018 - 02:34
can you check f
Excelchat Expert
25/07/2018 - 02:34
f30
Excelchat Expert
25/07/2018 - 02:35
=IF(and(SUM(H1:H28)>0,SUM(G1:G28)>0),SUM(F1:F28)-SUM(C1:C28),SUM(F1:F28))
Excelchat Expert
25/07/2018 - 02:36
this is the general formula for the conditions your looking for
Excelchat Expert
25/07/2018 - 02:36
you may have to change the references based on your actual data like SUM(H1:H28000)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
