CODEWITHSUNDEEP
google-sheets
apgsov
apgsov

Reputation: 904

Calculations Dependent On Multiple Drop-down Selections

I have the following spreadsheet calculating my household outgoings with a number of categories to choose from multiple drop-down lists (Cols B, C).

Spreadsheet

In Column H, with the header Outgoings, I would like to sum the columns D and E in two rows based on the selections made in the drop-down lists in J2, J3 which act as filters for said calculation. These drop-down lists reflect the Bill Type in Column B, and the Category in Column C.

As an example, if I selected from the drop-downs in Column J: Personal, Credit/Debit then I would like the two rows in Column H (Outgoings) to be populated with the total sum of the rows D and E, respectively, for where the Bill Type and Category in B and C match Personal and Credit/Debit.

Along with the above conditions, if there is no selection in either of the Column J drop-downs, I would like the rows in Column H (Outgoings) to simply provide the calculation of the incomes in D, E (Row 2) minus the remaining sum of each column (total outgoings, i.e. D2:D).

I'm a little new with the syntax required to perform more complex calculations and was wondering if this was achievable in spreadsheets, specifically, in Google Sheets.

Upvotes: 0

Views: 621

Answers (1)

Aresvik
Aresvik

Reputation: 4630

Try this in cell H2:

=if(J2&J3="",
sum(filter(D:D,C:C="Income"))-sum(filter(D:D,C:C<>"Income")),
sum(filter(D:D,B:B=J2))+sum(filter(D:D,C:C=J3))
)

enter image description here

This in H3:

=if(J2&J3="",
sum(filter(E:E,C:C="Income"))-sum(filter(E:E,C:C<>"Income")),
sum(filter(E:E,B:B=J2))+sum(filter(E:E,C:C=J3))
)

enter image description here

enter image description here

Upvotes: 2

Related Questions