# sumproduct - multiple criteria that includes partial text

#### stevemc2

##### New Member
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### AhoyNC

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
DanteAmor, You're right. Good catch, thanks.

#### stevemc2

##### New Member

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.

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

Last edited:

#### AhoyNC

##### Well-known Member
Maybe change your formula to a SUMIFS:
The one below would not include "fund" or "exclude"

Code:
``````[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]
[/TR]
[TR]
[TD]Report!\$AQ\$8:\$AQ\$99999,"<>"&"Fund*",Report!\$AQ\$8:\$AQ\$99999,"<>"&"exclude")[/TD]
[/TR]
</tbody>[/TABLE]``````

#### stevemc2

##### New Member
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

##### Well-known Member
You're welcome. Thanks for the feedback.

Replies
4
Views
66
Replies
19
Views
276
Replies
3
Views
231
Replies
5
Views
474
Replies
1
Views
99