I have a nine digit number for which the first three numbers determine what team this number belongs

Copper Contributor

I have a nine digit number for which the first three numbers determine what team this number belongs to. I hve over 800 numbers that begin with the numbers 101 throuhg 129. I have five teams that we assign the numbers to. How do I write this to be automatically calculated? So, if 101000000 is assigned to Team 1, could I create a countif that says countif(C:C2, "101******)=1? Or something similar to this?

5 Replies

Hello Thomas

 

Are you looking for something like this?

=COUNTIFS(A1:A100,">=101000000",A1:A100,"<102000000")

Hi @Thomas McKissick,

 

Which function you want to do. You want to count them?

 

Use COUNTIFS function, if you want count of your team records.

 

For eg. If you want count of team '101', you  have to put this formula.

=COUNTIFS(C:C,">=101000000",C:C,"<102000000")

 

Like this you should do for 5 teams. Give starting number and end number.

Yah, the logic is unclear. With bit another interpretation if, for example, for the Team1 are assigned numbers starting from 101, 105, 129 the total of numbers assigned to Team1 could be calculated as

=SUMPRODUCT((INT(A:A/1E6)={101,105,129})*1)

 

It might help to use a helper column. Start a second column and use th formula

=left(A1,3)
and copy it down. then it is very easy to do a countif for each beginning number to count the number of members of each team.