Percentage of Cells starting with a range of numbers

t13190

New Member
Joined
May 1, 2019
Messages
3
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
Joined
Oct 10, 2011
Messages
4,346
Office Version
365
Platform
Windows
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

New Member
Joined
May 1, 2019
Messages
3
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
Joined
May 1, 2019
Messages
3
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
Joined
Oct 10, 2011
Messages
4,346
Office Version
365
Platform
Windows
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)
 

Forum statistics

Threads
1,082,586
Messages
5,366,476
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top