merging countif and sumproducts

**fontana** · 12-20-2010, 02:52 PM

Is there any way to merge these two formulas?
=COUNTIF(Active!$A$2:$A$426,"*construction*")
=SUMPRODUCT((Active!$G$2:$G$426>=11)*(Active!$G$2:$G$426<=20))

**NBVC** · 12-20-2010, 02:59 PM

Perhaps:

=SUMPRODUCT(ISNUMBER(SEARCH("construction",Active!$A$2:$A$426)*(Active!$G$2:$G$426>=11)*(Active!$G$2:$G$426<=20))

**fontana** · 12-20-2010, 03:53 PM

=SUMPRODUCT(((Active!$G$2:$G$426>=6)*(Active!$G$2:$G$426<=10))--(Active!$A$2:$A$426="*construction*"))

Thank you so much for your suggestion however it is not picking up the second half. I get the same count as if the filter by construction was not there.

s

**NBVC** · 12-20-2010, 03:59 PM

That doesn't look like my suggestion.

Although, I did forget one closing parenthesis.

Try this formula:

Please Login or Register  to view this content.

**fontana** · 12-20-2010, 04:09 PM

Thank you, It did work this time.

I appreciate it very much!

I was still trying to work with what i knew when it did not work the first time.

=SUMPRODUCT(--(Active!G2:G426>=11)*(Active!G2:G426<=20),--(Active!A2:A426="Cable Construction Locate"))

**NBVC** · 12-20-2010, 04:12 PM

There is no need to mix -- with * in the Sumproduct() function.. it's one or the other... and that might also depend on how your ranges are setup and the content.

Also, Sumproduct does not accept wildcards, so you need to use LEFT/RIGHT/MID functions if you know the location of the substring or ISNUMBER(SEARCH()) if the substring could be anywhere within the string as implied with *construction*