Percentage of Cells starting with a range of numbers

[t13190]

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]

Maybe something like this.
NOTE: area code in cell D1 of the example is entered as text.

Excel Workbook
	A	B	C	D
1	Data		Area Code	212
2	555-525-6000
3	212-566-8799		Count	3
4	888-252-5858
5	212-256-23235		Percent	50%
6	214-578-7878
7	212-545-2525
Sheet

 

[t13190]

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]

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]

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:

=SUM(--(TRANSPOSE(LEFT($A$3:$A$500,3))=($E$4:$E$30)))

If there could be blanks in cells E4:E30 try:

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

=COUNTA(A$3:$A$500)