How can I use SUMPRODUCT AND SMALL with further conditions?

Question

I am using this formula to sum the 10 smallest values and all is well.

=SUMPRODUCT(SMALL(G:G;ROW(INDIRECT("1:10"))))

But I would like to add a filter/condition: I only want to sum values the value in column B:B is "A" or "B".

Answer 1

This will work for you:

• G1:G100/(B1:B100="B") return an array of numbers in column G: G while column B:B = "B", mismatched values will return an error. so use aggregate to calculate their sum without ignoring the error, the result is a sum of 1 to 10

here's formula:

=SUMPRODUCT(AGGREGATE(15,6,G1:G100/(B1:B100="B"),ROW(1:10)))