Countifs - Start of Number & Wildcard to find remainder

[lookingforhelp1]

Hi there

I have a several background data sheets consisting of various bits of key data (always in the same format).

I also have a summary sheet where I will be grouping key data from the background sheet via indirect (and specifying the sheet name on the summary).

The background data consists of account numbers that starts with a set number, which is relative to the type of account, followed by a piece of core data in the cells next to it..

Account Dummy data example:
30000 > 39999 would be Account type 1
40000 > 49999 would be account type 2
80000 > 89999 would be account type 3

However, some accounts could have multiple accounts and have a letter on the end to seperate them, i.e.

31111A and 31111B (would belong to the same person).


For Account Type 1, I have managed to achieve the correct result of a count if the account type falls between (for example) 30000 and 39999, but I run into problems if the account has a letter at the end.

I'm absolutely stumped and can't seem to get wildcards to work with this method?

Thanks in advance!

[jason.b75]

As an example

=SUM(COUNTIFS(A:A,{">=30000",">=30000*"},A:A,{"<=39999","<=39999*"}))

If the criteria are in cells then you would need to nest the CHOOSE function as well.

[lookingforhelp1]

Thank you so much! Worked perfectly.