I want to sum specific values in column F, provided that the respective values in columns C and E have a unique match in columns O and Q. To make the logic easier, I would like to include a condition that the summing occurs only if the date in column B is before June 30, 2023.

I will give an example: in this case, I would like to sum the values in column F for all matches of "Pinco-fruit" in columns O and Q. So the result I expect is 8.

One last important thing: I need the whole operation to be performed with one formula. I have tried different combinations of SUMIFS, VLOOKUP, FILTER, ARRAYFORMULA, but I have not found the right one.

Thanks for the help!

B C ... E F ... O ... Q
9/3/22 Pinco ... Apple 3
9/3/22 Pinco ... Banana 5
9/3/22 Pinco ... Kiwi 4
6/30/23 Pinco ... Apple
6/30/23 Pinco ... Banana

1 Answer 1


Use filter() and match(), like this:

=sum(filter(F1:F, match(C1:C, O1:O, 0), match(E1:E, Q1:Q, 0)))

See filter() and match().


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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