• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct. Help!

P

prazad82

Member
Hello,


I need help with this formula


=SUMPRODUCT((Audits!$B$2:$B$179="*Tech*")*(Audits!$A$2:$A$179>=StartDate)*(Audits!$A$2:$A$179<=EndDate)*(Audits!$F$2:$F$179=Plan!ProductCategory))


When I use this formula, it does not show the result. Actually I need to count the audit type containing the text "Tech" within a date range for a product category.


Is there something wrong with the formula? Please advice
 
Hi prazad82


You cant use wildcard with SUMPRODUCT.

Try:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Tech",Audits!$B$2:$B$179,1)))*(Audits!$A$2:$A$179>=StartDate)*(Audits!$A$2:$A$179<=EndDate)*(Audits!$F$2:$F$179=Plan!ProductCategory))
 
Hi Prasad ,


Rewrite your formula as follows :


=SUMPRODUCT(ISNUMBER(SEARCH("Tech",Audits!$B$2:$B$179))*(Audits!$A$2:$A$179>=StartDate)*(Audits!$A$2:$A$179<=EndDate)*(Audits!$F$2:$F$179=Plan!ProductCategory))


Narayan
 
I have one more question.


=IF(SUMPRODUCT(ISNUMBER(SEARCH("Tech",Audits!$B$2:$B$179))*(Audits!$F$2:$F$179=Plan!ProductCategory))>0,1,"")


In this formula, it only searches for the text containing "Tech". I need to modify this formula so that it searches for multiple text criteria (in my case, "Tech" and "Temp")from the range (Audits!$B$2:$B$179) and give the result in count.
 
Hi Prasad ,


Enclose the multiple arguments ( Tech , Temp , ... ) in an array :


=IF(SUMPRODUCT(ISNUMBER(SEARCH({"Tech","Temp"},Audits!$B$2:$B$179))*(Audits!$F$2:$F$179=Plan!ProductCategory))>0,1,"")


However , if you want a count , just the IF will not do ; you need to put a SUM outside the above statement , since the inner SUMPRODUCT , even though it sums up , is being used with a greater than 0 condition. Otherwise , do away with the IF statement , and have just the SUMPRODUCT function.


Narayan
 
Try this alternative


Code: 
=SUMPRODUCT(COUNTIFS(Audits!$B$2:$B$179,{"Tech*","Temp*"},Audits!$F$2:$F$179,Plan!ProductCategory))
 
You must log in or register to reply here.
Back
Top