sumproduct - multiple criteria that includes partial text

[stevemc2]

Hi

I have a table of data with many rows an columns and I'm trying to count the number of records that meet numerous criteria. I've managed to get a lot of it to work but am stuck when it comes to one field that contains one or more partial string elements. My current formula is: -

=SUMPRODUCT(--(Report!$B$8:$B$99999=$B9),--(Report!$AJ$8:$AJ$99999=""),--(Report!$H$8:$H$99999<3652),--(Report!$L$8:$L$99999<=100000),--(Report!$AG$8:$AG$99999>=1000),--(Report!$K$8:$K$99999>0),--(ISNUMBER(SEARCH(Report!$AQ$8:$AQ$99999,{"funded","exclude"}))))

and this gives me a result. However I would like to search column AQ to count the records that only contain the text fund or exclude (as well as all of the other criteria). I have managed to get it to work with the word "funded" (result is 23in y list) but it won't count when I just use partial text (i.e. "fund"); this just returns zero. If I also add in "exclude" I end up with VALUE.

Could anyone help?

Thanks
Steve

 

[AhoyNC]

Maybe:
=SUMPRODUCT((Report!$B$8:$B$99999=$B9)*(Report!$AJ$8:$AJ$99999="")*(Report!$H$8:$H$99999< 3652)*(Report!$L$8:$L$99999< =100000)*(Report!$AG$8:$AG$99999>=1000)*(Report!$K$8:$K$99999>0)*(ISNUMBER(SEARCH({"fund*","exclude",Report!$AQ$8:$AQ$99999))))

 

[DanteAmor]

Hi @AhoyNC, Missing a Curly bracketand

Maybe:
=SUMPRODUCT((Report!$B$8:$B$99999=$B9)*(Report!$AJ$8:$AJ$99999="")*(Report!$H$8:$H$99999< 3652)*(Report!$L$8:$L$99999< =100000)*(Report!$AG$8:$AG$99999>=1000)*(Report!$K$8:$K$99999>0)*(ISNUMBER(SEARCH({"fund*","exclude"},Report!$AQ$8:$AQ$99999))))

 

[AhoyNC]

DanteAmor, You're right. Good catch, thanks.

 

[stevemc2]

Thanks DanteAmor. When I first read your reply I thought "No I haven't, I recall ensuring I had both curly brackets." Then I read the thread properly...
I'm now need a formula that does the opposite of my original query - I wondered whether the repliers to this post may be able to help here also. After calculating the results that are now based on the solution you have provided, I need to split this down in to those that match another criteria and those that don't - i.e. <>"Trade" and ="Trade" as shown in the two formulae below. But within both of these formulae I need to show the results where column AQ now does not include either "fund" or "exclude". I can't these formulae to work as I don't seem to have the nests bracketed correctly, however I am still struggling... Does this make sense - and any further help is appreciated.

=SUMPRODUCT((Report!$B$8:$B$99999=$B8)*(Report!$AJ$8:$AJ$99999="")*(Report!$H$8:$H$99999<3652)*(Report!$L$8:$L$99999<100000)*(Report!$AG$8:$AG$99999>=1000)*(Report!$K$8:$K$99999>0)*(Report!$AR$8:$AR$99999<>"Trade")*(ISNUMBER(SEARCH({"fund","exclude"},Report!$AQ$8:$AQ$99999))))

=SUMPRODUCT((Report!$B$8:$B$99999=$B8)*(Report!$AJ$8:$AJ$99999="")*(Report!$H$8:$H$99999<3652)*(Report!$L$8:$L$99999<100000)*(Report!$AG$8:$AG$99999>=1000)*(Report!$K$8:$K$99999>0)*(Report!$AR$8:$AR$99999="Trade")*(ISNUMBER(SEARCH({"fund","exclude"},Report!$AQ$8:$AQ$99999))))

And, also, thanks AhoyNC - you're formula worked. The same query above applies...!

 

[AhoyNC]

Maybe change your formula to a SUMIFS:
The one below would not include "fund" or "exclude"

[TABLE="width: 108"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=SUMIFS(Report!$AQ$8:$AQ$99999,Report!$B$8:$B$99999,"="&$B8,Report!$AJ$8:$AJ$99999,"="&"",Report!$H$8:$H$99999,"<"&3652,Report!$L$8:$L$99999,"<"&100000,[/TD]
[/TR]
[TR]
[TD]Report!$AG$8:$AG$99999,">="&1000,Report!$K$8:$K$99999,">"&0,Report!$AR$8:$AR$99999,"="&"Trade",[/TD]
[/TR]
[TR]
[TD]Report!$AQ$8:$AQ$99999,"<>"&"Fund*",Report!$AQ$8:$AQ$99999,"<>"&"exclude")[/TD]
[/TR]
</tbody>[/TABLE]

 

[stevemc2]

Thanks very much AhoyNC - that worked.

I had been trying to use SUMPRODUCT as I had migrated to that from SUMIFS to better solver an earlier problem - but as SUMIFS provides the solution I am more than happy to go with that.

Thanks again

 

[AhoyNC]

You're welcome. Thanks for the feedback.