Countifs with asterisk wildcard

A

allday79

New Member
Joined
Mar 21, 2017
Messages
5
My problem is: Count the number of companies in Pennsylvania with Sales exceeding $2,000 and whose telephone area code is (610). Hint: Use a wildcard asterisk (*).
I currently have =COUNTIFS(State,H290,Sales,">2000",Telephone,"610*").
Getting a value of 0.

Do i need to keep the () around the 610? Tried multiple ways of stating this and I am still getting 0. Should be 7.

Filtered data is below. Appreciate any help. Thank you

ST
 Sales (000 000) Telephone
PA $ 4,469(610) 694-2424
PA $ 2,515(610) 768-3300
PA $ 2,515(610) 768-3300
PA $ 2,515(610) 768-3300
PA $ 2,738(610) 644-1300
PA $ 2,037(610) 834-6301
PA $ 2,467(610) 378-0500

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
either one of these should work

=COUNTIFS(State,H290,Sales,">2000",Telephone,"*610*")

or

=COUNTIFS(State,H290,Sales,">2000",Telephone,"(610)*")
 
Last edited:
Upvote 0
Welcome to the Board!

Well, looks like you found someone to do the homework for you!;)

Let's say if we can help you understand it.
Just note that what you original formula does. Your condition is:
Code: 
[COLOR=#333333]"610*"[/COLOR]
So, that is looking for anything that STARTS with 610.
If the "6" is not the first character, you need to put a wildcard before it, or the character that might exist before it.
 
Upvote 0
Give this a try
=COUNTIFS(State,H290,Sales,">2000",Telephone,"610*")
 
Upvote 0
Give this a try
=COUNTIFS(State,H290,Sales,">2000",Telephone,"610*")
Click to expand...
I think that is the exact formula they already tried in the original post...
 
Upvote 0
None of these have worked. I have tried this formula multiple ways and still get 0 for a value.

Would the format of the phone number matter? below is the current format.

Also, the small table i included in the first post was just a portion of the data I am using. I filtered through 16 columns and 374 rows of data.

Thank you,
 
Last edited by a moderator:
Upvote 0
are you sure your sales values are numbers and not text ? (they 'look' like text to me)
what do you have in cell H290 ?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Help!
Replies
3
Views
1K
63falcondude
63falcondude

Forum statistics

Threads
1,214,525
Messages
6,120,052
Members
448,940
Latest member
mdusw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top