Sum of values with references on different rows

Question

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

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().