CODEWITHSUNDEEP
excelfiltercriteriasumproduct
Jesper Kindt Larsen
Jesper Kindt Larsen

Reputation: 107

How can I use SUMPRODUCT AND SMALL with further conditions?

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".

Upvotes: 0

Views: 578

Answers (1)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

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

enter image description here

Upvotes: 3

Related Questions