How to change value of field based on values of two other fields in Excel or Google Sheets

0
votes

I'm trying to create a customized personal expense report on Google sheets . If anyone has suggestions for a better approach, I'm open to to ideas!

In one column Category, I have option to select category with a drop down - Restaurants, Rent, Electricity etc. The column Value on the next to it holds the an integer value.

On a the same sheet, I have a column where all categories are defined. In the column Limit next to it, is the maximum integer value for each category. The next column Balance holds the remaining value (Limit - Sum of all Value matching Category)

My question is - when I add an entry in sheet 1 for any category with a value, how do I subtract the Value added from the respective Category to show the remaining balance from Limit in Balance column? As I keep adding items, the Balance field should get updated.

TABLE 1

Item Category Value
i1     Rest    100
i2     Rent    50
..
..

TABLE 2 (In same sheet somewhere adjacent to the above table)

Category Limit Balance
Rest      500    400
Rent      1000   950
..
..
1
excelif-statementgoogle-sheetssumifs
if you are using Google Sheets why tag it Excel? – Scott Craner
@ScottCraner I'm open to suggestions doing it via Excel too. – Suraj Bhatia
Sumifs should do what you want. – Scott Craner
share a copy of your sheet with example of desired output – player0

1 Answers

2
votes

Try using this:

=IFNA([limitcellname]-SUMIF([rangeofcategories],[categorycellname],[expensevaluerange]),[limitcellname])

Range of categories = column where all categories are listed in the expenses table. E.g- B:B if B column has expense categories

expense value range = column where all expenses are listed in the expenses table. E.g- C:C if expenses values are in C column

category cell name is the category cell in the balance table

limit cell name is the limit cell

This should work for your purposes