CODEWITHSUNDEEP
excelexcel-formula
Djhans26
Djhans26

Reputation: 79

How to add cells if conditions are met

I am trying to create a statement that I send to customers showing how much money they owe us. In the balance column, I need it to have a rolling total but can't seem to figure the formula out. Below is my current formula.enter image description here

=IF(AND(ISBLANK(I23),ISBLANK(K23)),"",IF(B23="retention",0,IF(B23="payment",OFFSET(M23,-1,0,1,1)+I23-K23,IF(NOT(OR(B23="retention",B23="payment")),OFFSET(M23,-1,0,1,1)+I23-K23,OFFSET(M23,-2,0,1,1)))))

In the last row, it should total that of 15,500 plus the charges amount(500) minus payments totaling 16,000.

Upvotes: 0

Views: 46

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

use:

=IF(AND(I23="",K23=""),"",IF(B23="retention",0,SUMIFS($I$23:$I23,$B$23:$B23,"<>retention")-SUM($K$23:$K23)))

Where 23 is the first row in which the formula is placed, then copied down.

enter image description here

Upvotes: 1

Related Questions