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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,410
Office Version
365
Platform
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
10,221
Office Version
2007
Platform
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,410
Office Version
365
Platform
Windows
DanteAmor, You're right. Good catch, thanks.
 

stevemc2

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

Watch MrExcel Video

Forum statistics

Threads
1,089,923
Messages
5,411,266
Members
403,354
Latest member
JolynnCh

This Week's Hot Topics

Top