Microsoft Office 2010 professional

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?

=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.

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?

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.

Sounds good, thanks, DerbyDad!

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 be

out of trouble, I've used Column XX as an example.XX 1) New to Bank 2) Account Opening-POS 3) Medium Risk 4) IBCNote cell XX4 contains only your desired three character search string of IBC

Now 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 Z

Edit: OOPS on the formula, should be:

=COUNTIFS(M:M,XX1,N:N,XX2,F:F,XX3,Z:Z,"="&XX4&"*")

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History