CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formula
Nico
Nico

Reputation: 45

Use result of filter formula twice

can you help me to simplify a formula, please? I am setting up an accountants sheets and need to subtract incoming payments from outgoing payments in a certain time frame. As there are several filter criteria and a lot of entries in the table, I was wondering what the best way is to do the filter only once.

Here is a minimum example

This is my filter function in F4

=sum(filter(B1:B8,C1:C8>=F1,C1:C8<=F2,A1:A8="expense"))-sum(filter(B1:B8,C1:C8>=F1,C1:C8<=F2,A1:A8="income"))

Here is the table for you.

Is there any way like sum(filter(expense-income))?

Upvotes: 1

Views: 83

Answers (1)

kishkin
kishkin

Reputation: 5325

Try this in F4:

=sum(filter(IF(A:A = "income", -1, 1) * B:B, B:B <> "", C:C >= F1, C:C <= F2))

enter image description here

Upvotes: 2

Related Questions