# Percentage of Cells starting with a range of numbers

#### t13190

##### New Member
I have a project that I'm working on and running into a problem. I am trying to calculate if a phone number starts with specific area codes. I want to count them and get a percentage of the total ones in a sheet that i can add to. I would like to eventually do conditional formatting for easy viewing.

I'm currently using =SUMPRODUCT(--ISNUMBER(SEARCH(inmarketnum,A3,1)))>0 to get the numbers to indicate true false if they contain the numbers in inmarketnum. How can i search just the first three numbers vs the whole string that it currently does? And how to I create a percent of the True/False?

#### AhoyNC

##### Well-known Member
Maybe something like this.
NOTE: area code in cell D1 of the example is entered as text.
Excel Workbook
ABCD
1DataArea Code212
2555-525-6000
3212-566-8799Count
4888-252-5858
5212-256-23235Percent
6214-578-7878
7212-545-2525
Sheet

• t13190

#### t13190

##### New Member
Thank you. I think I am getting closer. I filled in the correct cells in the formula and it didn't work. Can you see where my mistake is ? I think some of it is that i have blank cells in A3-A500 as i need them fillable by the user. The area codes i have listed are E4-E30.

=SUMPRODUCT(--(LEFT(\$A\$3:\$A\$500,3)=\$E\$4:\$E\$30))

#### t13190

##### New Member
Thank you I think that i am close now. I filled in the fields as they correspond to my spreadsheet and i broke it somehow. The fields that the numbers are in are A4-A500 with some fields blank as i need the end user to be able to fill them in as they come in. The area codes i am checking against are fields E4-E30. Am i doing this correctly or missing something?

=SUMPRODUCT(--(LEFT(\$A\$3:\$A\$500,3)=\$E\$4:\$E\$30))

#### AhoyNC

##### Well-known Member
The issue is you have uneven ranges (A3:A500 vs E4:E30).

Both of these formulas are array formulas and must be entered with CTRL-SHIFT-ENTER. If done right Excel will put {} brackets around the formula.

If there will not be any blank cells in your E4:E30 range try:
Code:
``=SUM(--(TRANSPOSE(LEFT(\$A\$3:\$A\$500,3))=(\$E\$4:\$E\$30)))``
If there could be blanks in cells E4:E30 try:

Code:
``=SUM(--(TRANSPOSE(LEFT(\$A\$3:\$A\$500,3))=(IF(\$E\$4:\$E\$30<>0,\$E\$4:\$E\$30))))``
To get a count of total non-blank cells in range A3:A500:

Code:
``=COUNTA(A\$3:\$A\$500)``