sum if with condition

Copper Contributor

Hi all, please kindly help me create this excel formula

this is the source of data:

Disburse AmountStatus
Rp300.000.000Ongoing
Rp500.000.000Ongoing
Rp200.000.000Ongoing
Rp150.000.000Paid
Rp20.000.000Late

 

the formula i want to create

Umbrella Limit:Rp 25.000.000.000 (hardcode)
Loan Paid:Rp 150.000.000 (all rows with status of "Paid") 
Loan Outstanding:Rp 1.000.000.000 (all 3 rows with status of "ongoing") 
Available Limit:umbrella limit: all "ongoing" and "late" rows. which is Rp 1.020.000.000. and i want this available limit to exclude the "Paid" row from the source data

 

the formula i want to create is the Loan Paid , Loan Outstanding and Available limit. 
thanks

1 Reply

@anna76 

 

Hi, you can use the SUMIF function for that. Please see attached sample document with the proposed solution.

 

Martin