Google-sheets – How to calculate SUM of specific row range based on date

google sheets

How to calculate the remaining amount to spend for a specific day (that day is today).

So, I have the budget for one month (let's say 1000$).
My daily spending is 32.25$.
If I spent 20$ today, is there a possibility to see how much is left for today?

So basically we have 3 steps.

  1. SUM all transactions from today's date

  2. Subtract from the daily budget(I can put it in a separate cell, or I can put it in the same cell)

  3. Show the result in cell

    Link to the sheet: https://docs.google.com/spreadsheets/d/1AhreVRWJyMoG5JR-qvtK3Jq9K98G3L_-oIn6ju0Ssmk/edit?usp=sharing

Best Answer

Edited answer

(following OP's comment on the sheet: "Daily budget would be determined by Planned expenses (C22 cell) divided by number of days in month.")

Your final needed formula would be: 

=(SUMIF(Transactions!B5:B44;"="&(TODAY());Transactions!C5:C44))-
       C22/(EOMONTH(TODAY();0)-(EOMONTH(TODAY();-1)))

To summarize, these are the 3 formulas you will need to get your expected result:

+-------------------------+------------------------------------------------------------------+-------------+
|      We used A-B=C      |                             Formulas                             | Test Result |
+-------------------------+------------------------------------------------------------------+-------------+
| A. today's transactions | =SUMIF(Transactions!B5:B44;"="&(TODAY());Transactions!C5:C44)    | ₩360.700    |
| B. daily budget         | =C22/(EOMONTH(TODAY();0)-(EOMONTH(TODAY();-1)))                  | ₩34.448     |
| C. remaining for today  | =(SUMIF(Transactions!B5:B44;"="&(TODAY());Transactions!C5:C44))- |             |
|                         |    C22/(EOMONTH(TODAY();0)-(EOMONTH(TODAY();-1)))                | ₩326.252    |
+-------------------------+------------------------------------------------------------------+-------------+

Note: One may have to replace ; with , according to the sheet,s locale.

Original answer

If I understand you well, the formula you would need is:

=(SUM all transactions from today's date)-(daily budget) 

=(SUMIF(Transactions!B5:B44;"="&TODAY();Transactions!C5:C44))-(daily budget)

You can place the formula in cell L10 (Today left) to see the result.

Note: I don't understand what you mean or how you calculate your "daily budget" so I leave this part for you.
Still. If you need further assistance please let us know.

Related Topic