sumproduct - multiple criteria that includes partial text

stevemc2

New Member
Joined
Mar 1, 2013
Messages
11
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
 

Some videos you may like

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
Joined
Oct 10, 2011
Messages
4,654
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
Joined
Oct 10, 2011
Messages
4,654
Office Version
  1. 365
Platform
  1. Windows
DanteAmor, You're right. Good catch, thanks.
 

stevemc2

New Member
Joined
Mar 1, 2013
Messages
11

ADVERTISEMENT

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...!
 
Last edited:

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,654
Office Version
  1. 365
Platform
  1. Windows
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]
[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

New Member
Joined
Mar 1, 2013
Messages
11
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
Joined
Oct 10, 2011
Messages
4,654
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,652
Messages
5,549,210
Members
410,905
Latest member
Extjel
Top