CODEWITHSUNDEEP
excel-formula
ASH
ASH

Reputation: 20362

Sumproduct with Three Criteria and

I am trying to figure out how to search the for the left most character in a cell, which will always be 1 or 2, and then search for a short string, like ‘Flow’ inside of a longer string, like ‘System Flow’ or ‘Flow Control’, and if those two criteria are met, then take an average of the numbers in the corresponding rows, in Column M. So, this is the third criteria. I came up with the function below, but it always returns 0, which is not correct.

=SUMPRODUCT((LEFT(K38:K44,1)=C38)*(ISNUMBER(FIND(D38,L38:L44))))*M38:M44

Any thoughts on what is wrong here? Thanks!

Upvotes: 0

Views: 150

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use AVERAGEIFS():

=AVERAGEIFS(M38:M44,K38:K44,C38 & "*",L38:L44,"*" & D38 & "*")

Edit, to deal with the change of numbers and strings we need to adjust your formula a bit:

=SUMPRODUCT((LEFT(K38:K44,1)=TEXT(C38,"@"))*(ISNUMBER(SEARCH(D38,L38:L44)))*M38:M44)/SUMPRODUCT((LEFT(K38:K44,1)=TEXT(C38,"@"))*(ISNUMBER(SEARCH(D38,L38:L44))))

Upvotes: 2

Related Questions