# Solved COUNTIFs statement getting too long, ideas to shorten it?

Microsoft Office 2010 professional
August 6, 2013 at 10:21:30
Specs: Windows XP
 Working in Excel 2010, I use a countif for data like how many major errors came in for a new product, how many came in on a new client, how many were by a certain analyst, etc. There are at least fifteen of these statements for different fields, the longest one uses three criteria as show in this countif... COUNTIFS('Raw Data'!M:M,"New to Bank",'Raw Data'!N:N,"Account Opening-POS",'Raw Data'!F:F,"Medium Risk")Now we're about to add another criteria, which will be a split of business types so add a 'Raw Data'!B:B,".... and there are EIGHT possibities with TWO of them needing to be counted together. Obviously looking for one business type won't be difficult but picking out the two that start with 'IBC' is what I need help with. To count the IBC cases, I need to either exclude the other six types or figure out how to count just the IBC.Is there a 'starts with...' function? The IBC names are rather lengthy and I'm worried about making me statement too long. I'm hoping someone with more Excel experience will have a better idea.Anyone?

See More: COUNTIFs statement getting too long, ideas to shorten it?

#1
August 6, 2013 at 11:47:11
 =COUNTIFS(A1:A10,"=IBC*",...)The asterisk is used as a wildcard.You can also use SUMPRODUCT:=SUMPRODUCT(--(LEFT(A1:A10,3)="IBC"))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
August 6, 2013 at 11:59:49
 The asterisk is perfect, thanks! And just for future reference, the LEFT in the sumproduct means starting at the left? And the 3 is saying 'the first three characters', so the 1st 3 chars at the left equal IBC, then count?

Report •

#3
August 6, 2013 at 12:22:32
 Look up the RIGHT, LEFT amd MID functions in Excel Help or via a web search.They are text based functions for extracting portions of a string.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
August 6, 2013 at 12:29:13
 Just a suggestion, but if you want to shorten up your COUNTIFS() a bit more try this:Take your search strings and put them in a column tucked away where it will beout of trouble, I've used Column XX as an example.``` XX 1) New to Bank 2) Account Opening-POS 3) Medium Risk 4) IBC ```Note cell XX4 contains only your desired three character search string of IBCNow you can write your COUNTIFS like this:=COUNTIFS(M:M,A1,N:N,A2,F:F,A3,Z:Z,"="&A4&"*")I don't know what column your IBC strings will be in, so I used column ZEdit: OOPS on the formula, should be:=COUNTIFS(M:M,XX1,N:N,XX2,F:F,XX3,Z:Z,"="&XX4&"*")MIKEmessage edited by mmcconaghy