Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel- Countif function won't work for text fields - what can I use? 1

Status
Not open for further replies.

OMoore

Technical User
Joined
Oct 23, 2003
Messages
154
Location
EU
Hi all,

I need to count fields in excel that contain numbers and text... e.g

Column A:
6742-1-109
3345-7-521
65-8459
6742-1-109

The number of unique fields above would be 3.

Countif function only seems to work with numbers. What can I use instead??

Thanks a lot.

Owen
 
Owen,

Do a PivotTable or use Advanced Filter - Unique. Either will give you a UNIQUE LIST. Count the entries in the list.

Skip,

[glasses] [red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [tongue]
 
COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples".

Remark

Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.

Example


A B
1 apples 32
2 oranges 54
3 peaches 75
4 apples 86
5 bananas 45

Formula:
=COUNTIF(A2:A5,"apples")
Description:
Number of cells with apples in the first column above
Result:
(2)

Does that help?


misscrf

Management is doing things right, leadership is doing the right things
 
Skip,
The number of unique fields will be used to update graphs automatically. I need a method of counting the number of unique entries without doing it manually. Does your method allow this?

misscrf,
I understand the formula you've laid out but it would require me to list every possible text derivation in the alphabet - am I right in saying this or have i gotten it wrong?

Thanks again.
Owen
 
To get the number of unique values use

=SUM(1/COUNTIF(A2:A1000,A2:A1000))

entered as an array formula, by using Ctrl-Shift-Enter instead of Enter ( this will put curly brackets around the formula afterwards ).



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Thanks Tony and Glen - it works perfectly!!!!

Owen
 
Hi guys,

Can I ask something, using the countif forumla,for the criteria part, i need to count the number of occurences for a numeric range from 4-6, how do I represent this in excel?

Regards,
Dino
 
Hi Dino,

You can't do that with COUNTIF, but SUM or SUMPRODUCT can do it easily:

[blue][tt] =SUM((A1:A20>=4)*(A1:A20<=6))[/tt][/blue] array-entered (i.e. with Ctrl+Shift+Enter) or

[blue][tt] =SUMPRODUCT((A1:A20>=4)*(A1:A20<=6))[/tt][/blue] entered normally (with Enter)

Both of these create a value of 0 or 1 (actually TRUE or FALSE coerced to 1 or 0 respectively but don't worry about that) for each cell in the range and then sum these values, which has the same effect as counting those that satisfy the criteria.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top