New Contributor

Excel formula

I have a spreadsheet. I need a formula to add 2 cells together, one of the cells will be a different cell depending if there is a value in another cell I have tried sumifs with the value for the 3rd cell as either <0 or >0 but can't get it to work also tried countifs

My answer needs to be sum of A+D if B is less than zero or sum of C+D if B is more than zero

6 Replies

Re: Excel formula

``=IFS(B1<0,SUM(A1,D1),B1>0,SUM(C1,D1),TRUE,0)``

Last argument if b1 =0 then 0

Re: Excel formula

Thank you, it works if the value in B1 is more than 0, however if it is 0 or left blank then the answer is blank so I have to put in a minus value, tried changing the first part to B1BLANK but that didn't work

Re: Excel formula

What do you want to return if B1 is blank? And if B1 contains 0?

Re: Excel formula

if B1 is blank or 0 I want to return the sum of A1 and D1

Re: Excel formula

Change the formula to

=IF(B1<=0,SUM(A1,D1),SUM(C1,D1))

or

=SUM(IF(B1<=0,A1,C1),D1)

Thank you