Google-sheets – Filter based on Data Validation drop down list

google sheets

So I have a two part question that involves filtering a column based off of both data validation drop down lists, and dates.

The problem for me is to create a filter that will give me a subtotal based off both the month and the expense/fixed asset columns, then apply it to the subtotal cell J25. Someone earlier had showed me how to do it by month, but I'm also interested in applying both filters. I've looked through the function list, and can't seem to figure this out.

Afterwards, I'd like to take that subtotal and apply it into another sheet that contains cash flow information. If I know how to do the above, I can probably figure this part out. But I figure I might as well ask anyways.

Best Answer

Okay so I solved this problem with this function:

=sum(filter(J6:J25,K6:K25="Meals",month(B6:B27)=7))

This would give me the subtotal for all Meal expenses for the month of July. This is a bit of a pain to apply when referencing this data in other sheets, but it works.

Related Solutions

Google-sheets – Calculate total based on drop down criteria

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.

Google-sheets – Filter and subtotal by month

You don't want to filter the whole column if you need subtotals somewhere below the data. If you filtered the whole column, the subtotal row would be filtered out. So, to filter the data for April:
1. Select your data table
2. Data > Filter
3. Small arrow by the header of the needed column > Filter by condition... > Custom formula is > enter this formula: =month(A2)=4 > then OK
  
  (My dates are in column A)
For subtotals just use the formula below your data like this: =subtotal(9,D2:D7)
In a separate sheet I would use a formula which is not dependent on the filter, like this:
=sum(arrayformula(Sheet1!D2:D7*(month(Sheet1!A2:A7)=4)))
If your sheet name consists of several words use commas when referencing it like this:
'My data'!A2:A7

Best Answer

Related Solutions

Google-sheets – Calculate total based on drop down criteria

Google-sheets – Filter and subtotal by month

Related Topic