**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.*