Google-sheets – Calculate total based on drop down criteria

google sheetsworksheet-function

I am creating a budget that has different expenses on one worksheet (Expense_Overview). On another worksheet, I have a list of expenses (Expense_Detail) I am entering that are categorized based on a data validation criteria I set via a list of items. I would like for the Actual Expense column in the Expense_Overview to tally the total Expense Category based on the data in Expense_Detail. Eg, I want to know the total of all the Lighting expenses.

I want to avoid pivot tables if possible. I am not comfortable with them, and I am hoping a simple SUMIF line will accomplish this.

Here is my sheet
https://docs.google.com/spreadsheets/d/1XYuo1ozmHkeAnfg00gEmr9-7c8OMCTSSUM7FNm1LOnE/edit?usp=sharing

Best Answer

Please try, in B2 of Expense_Overview: 

=sumif(Expense_Detail!D:D,A2,Expense_Detail!C:C)

copied down to suit, or: 

=ArrayFormula(sumif(Expense_Detail!D:D,A2:A,Expense_Detail!C:C))

You have a treat in store whenever you get around to pivot tables. They are very powerful but incredibly easy to use for all that:

Select A1:D7 in Expense_Detail, Data, Pivot table report..., for Rows Add field select Expense Category, for Values Add field select Amount.

PS Remove trailing space from A3 in Expense_Overview.

PPS If a pivot table will do the job it is normally far and away the best (and often easiest!) solution.

Related Topic