excel formula - Sumproduct with Three Criteria and -


i trying figure out how search left character in cell, 1 or 2, , search short string, ‘flow’ inside of longer string, ‘system flow’ or ‘flow control’, , if 2 criteria met, take average of numbers in corresponding rows, in column m. so, third criteria. came function below, returns 0, not correct.

=sumproduct((left(k38:k44,1)=c38)*(isnumber(find(d38,l38:l44))))*m38:m44 

any thoughts on wrong here? thanks!

use averageifs():

=averageifs(m38:m44,k38:k44,c38 & "*",l38:l44,"*" & d38 & "*") 

edit, deal change of numbers , strings need adjust formula 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)))) 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -