Search code examples
excelexcel-formulaformulaseconomics

EXCEL: SUM = Total, MINUS other cell, if value in 3rd cell = "Specific text"

So I have a dilemma, I really hope some of you bright minds out there can help me. I'm trying to make a budget. In the budget, I include status of a payment. Some payments are "automatic transactions" (ie. where money sits in one account and a service provider extract them at a certain date).

I also have a "real-time" budget next to it where I want to be able to fill out the CURRENT balance of each account (by checking the balance, then manually changing the value). This is where the issue starts - and my competence ends.

I want to be able to predict my budget for the remaining days of the month, based on the current account balances, and in regard to the fact that some payments (the automatics) have not yet been drawn. This way, the budget is adjusted in relation to over and under spending (which happens).

The basics of the sheet layout are below, and I "simply" want:

Realtime_budget value of [Total] to be:

SUM of Realtime_accounts MINUS amounts in Budget where the corresponding status = 'Unpaid' or 'Not Drawn'.

From here on the "Title" = Table name, and column title is, just that.

Table name: Budget

 [Status]   [Amount]    

 Paid       500
 Unpaid     400
 Not drawn  450

Table name: Realtime_accounts

 [Account]  [Amount]

 Bills      0
 Other      450 
 Automatics 400

Table name: Realtime_budget

 [Total]            [Daily]

 Realtime amount    Realtime amount / daily
Solution

  • In what I understood from your question the below formula will work.

    =SUM(Realtime_accounts[Amount])-SUMIF(Budget[Status],"Unpaid",Budget[Amount])-SUMIF(Budget[Status],"Not drawn",Budget[Amount])

    enter image description here

    But I feel its trivial. wonder if I fully took in the question. Let me know.