Find category then calculate outcome

[anon51997840]

Hi there!
I have this sheet that sould track all expenses but how do you FIND every category that is under food and added them together (below in red)

I would like to have the following but do not know how:

1. to select a category from a certain list. (H colomn)
2. line 8, colomn E for example, should calculate something like this: amount of line 6 + line 7 - (minus) every food costs that has the label ‘food’ as a category. = then display what is left this month for the food category.

See example attached.
test.ods (30.2 KB)

[erAck]

See attached
103731-ask.ods (33.1 KB)
I also added corresponding formulas in row 73.

Btw, the SUM(...) around expressions like E6+E7-SUM(....) i.e. =SUM(E6+E7-SUM(....)) is superfluous, it only sums one argument, the result of the inner expression.

Also, showing all hidden rows revealed that the data in I14:K20 is shifted to the right by one column, so that would never be calculated, but that might be on purpose (though confusing). Note that the new formulas include all rows from 14 to 69.

[anon51997840]

Thanks this solved it.

I only want one other thing and that is how do i get a dropdown menu of the category’s in colomn G.
When i enter a new row of expenses i can select a category.

[AllanR]

@anon51997840

how do i get a dropdown menu of the category’s in colomn G.

Apply Data Validation to the Input Category cells as follows:

• Select cells H23:H69

• Then select Menu > Data > Validity > Criteria tab > set Allow= “Cell range”, untick “Allow empty cells” > check Source= $Uitgaven.$E$4:$K$4 (the allowable Category values) > OK

• Click in any cell in range H23:H69 and observe pull-down Category selection.

I’ve uploaded a modified version erAck’s file to illustrate.
103731-plus-validity-ask.ods (32.3 KB)