Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Countif Criteria Reference Cell

How can I use Countif to allow me to reference a cell that contains the area
codes of phone numbers to be counted? Below is my feble attempt:

=COUNTIF(Contacts!H3:H229,C4&"*")

where, C4 is the cell that contains the area codes to be searched. The "*"
must be employed, as the area code is only the beginning 3 digits of the 10
digit numbers in the given range.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countif Criteria Reference Cell

What format are the phone numbers in?

(123) 123-1234
123-123-1234
123 - 123 - 1234
1231231234

Do any of the area codes start with leading 0s?

--
Biff
Microsoft Excel MVP


"StephonM" wrote in message
...
How can I use Countif to allow me to reference a cell that contains the
area
codes of phone numbers to be counted? Below is my feble attempt:

=COUNTIF(Contacts!H3:H229,C4&"*")

where, C4 is the cell that contains the area codes to be searched. The "*"
must be employed, as the area code is only the beginning 3 digits of the
10
digit numbers in the given range.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Countif Criteria Reference Cell

They are in the following format:

212 555 8888

"T. Valko" wrote:

What format are the phone numbers in?

(123) 123-1234
123-123-1234
123 - 123 - 1234
1231231234

Do any of the area codes start with leading 0s?

--
Biff
Microsoft Excel MVP


"StephonM" wrote in message
...
How can I use Countif to allow me to reference a cell that contains the
area
codes of phone numbers to be counted? Below is my feble attempt:

=COUNTIF(Contacts!H3:H229,C4&"*")

where, C4 is the cell that contains the area codes to be searched. The "*"
must be employed, as the area code is only the beginning 3 digits of the
10
digit numbers in the given range.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Countif Criteria Reference Cell

Try it like this:

=SUMPRODUCT(--(LEFT(Contacts!H3:H229,3)=C4&""))

--
Biff
Microsoft Excel MVP


"StephonM" wrote in message
...
They are in the following format:

212 555 8888

"T. Valko" wrote:

What format are the phone numbers in?

(123) 123-1234
123-123-1234
123 - 123 - 1234
1231231234

Do any of the area codes start with leading 0s?

--
Biff
Microsoft Excel MVP


"StephonM" wrote in message
...
How can I use Countif to allow me to reference a cell that contains the
area
codes of phone numbers to be counted? Below is my feble attempt:

=COUNTIF(Contacts!H3:H229,C4&"*")

where, C4 is the cell that contains the area codes to be searched. The
"*"
must be employed, as the area code is only the beginning 3 digits of
the
10
digit numbers in the given range.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF cell reference won't work Kenny Newbry Excel Worksheet Functions 4 June 26th 07 07:59 PM
Countif criteria in another cell rclough Excel Discussion (Misc queries) 3 February 13th 07 11:24 PM
COUNTIF less than cell reference stevepain Excel Discussion (Misc queries) 3 July 12th 05 04:17 PM
Could the "Criteria" in COUNTIF function be a cell reference? JohnSheenWSN Excel Worksheet Functions 2 June 16th 05 08:07 PM
Countif with cell reference Nick Excel Worksheet Functions 2 May 6th 05 04:48 PM


All times are GMT +1. The time now is 06:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"