2

I was creating an accounting log, so right now I'm using:

   =SUM(FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E="Summary!C65"),FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E=Summary!C66),FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E=Summary!C67),FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E=Summary!C68))

The first two criteria in the filter function check if the range is within a specific date range, and the final criterion checks if the type of transaction is relevant. But it's not letting me compare multiple values so I have to keep repeating the filter function.

Posting the same formula for more clarity

=SUM(FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E="Nuts"),FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E="Bolts"),FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E="Screws"),FILTER(Transactions!C:C,Transactions!B:B>=D23,Transactions!B:B<=E23,Transactions!E:E="Clips"))

Is there a way for me to just compare if the transaction done is "Nuts, Bolts, Screws or Clips" in one go without having to repeat the filter function?

1

Can't test if it will work without a sample spreadsheet, but try

   =SUM(FILTER(
     Transactions!C:C,
     Transactions!B:B>=D23,
     Transactions!B:B<=E23,
     MATCH(Transactions!E:E, Summary!C65:C68, 0)
    ))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.