Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Countif & Contains?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have a series of phone numbers & I'm trying to determine the number of times a particular area was called

864384XXXX
864316XXXX
864288XXXX
864288XXXX
864232XXXX
864384XXXX
864384XXXX

I thought I could use a formula like this: =COUNTIF($E$47:$E$322,"*864*")

(Notice the ' * ')

Where in this column, if the number contained an '864' number, it would count it.

What's wront with this forumula or what is the correct formula?

Thanks


Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Generate a Non-Repeating List of Random Numbers in Excel - UDF
- Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great funct

Similar Topics







Hi guys,

I'm analyzing my buddy's phone bill for T-Mobile - I've downloaded all the call usage onto a spreadsheet and I wanted to know if there is a way I can determine the number of times any phone number shows up in a certain column.

Previously, for my phone bill, I just created a formula to count the occurance of the 5 phone numbers that I *felt* I had dialed the most with a COUNTIF formula.

But I have no idea which numbers this guy calls the most. There's close to 1500 rows of data and I'd probably go blind trying to do a COUNTIF on every number he's ever dialed.

So to recap: is there a quick way to ask excel to show me the number of times every phone number shows up in a certain column without using a COUNTIF formula that requires me to manually type in every phone number?

I might not even be making sense with my question, but any advice is good advice. Thanks in advance.

-Bill


I want to countif the occurrences between a certain average result/number in
my cell V66 and cero. Because my data would be changing over time, the
numbers of occurences will be higher or lower. How do I combine the following
to countif and always get a positive number?:

- If I use my formulae =COUNTIF(V5:V64,">"&V66)-COUNTIF(V5:V64,">0"), I get
the correct number, but it is a negative number (e.g.-25)

- If I reverse the order in the formulae above:

=COUNTIF(V5:V64,">0")-COUNTIF(V5:V64,">"&V66), I still get the correct
number and it is positive (e.g. 25).


Thank you



I want to countif the occurrences between a certain average result/number in
my cell V66 and cero. Because my data would be changing over time, the
numbers of occurences will be higher or lower. How do I combine the following
to countif and always get a positive number?:

- If I use my formulae =COUNTIF(V5:V64,">"&V66)-COUNTIF(V5:V64,">0"), I get
the correct number, but it is a negative number (e.g.-25)

- If I reverse the order in the formulae above:

=COUNTIF(V5:V64,">0")-COUNTIF(V5:V64,">"&V66), I still get the correct
number and it is positive (e.g. 25).


Thank you



I would like to use the COUNTIF command in conjunction with the AND command so that I can have Excel Count if column a is one value and columb is another value.

.....A.............B...
1..Red..........Box
2..Green.......Hat
3..Blue.........Box
4..Blue.........Hat
5..Black........Box
6..Red..........Hat
7..Red..........Box

Normally one would use COUNTIF to count the number of times RED appears in column A or the number of times BOX appears in column B, is what I need to do is count the number of times Red is in column A and Box is in the adjcent cell in column B.

I have tried these, but they dont work, I must be overlooking something obvious:
=AND(COUNTIF(A1:A7,"Red"),COUNTIF(B1:B7,"Box"))
=COUNTIF(A1:A7,"Red")+COUNTIF(B1:B7,"Box")
=COUNTA(A1:A7="Red",B1:B7="Box")


I wrote this formula in attempts to count the number of unique values in a column that must meet certain criteria and cannot match what is in any of the 5 cells in the second sheet. I've tried to correct the formula numerous times with no luck.

Can anyone see what I am missing?

=COUNTIF('FA 08-20-10'!M:M,"<>Sheet1!A18")-COUNTIF('FA 08-20-10'!M:M,"<>Sheet1!A19")-COUNTIF('FA 08-20-10'!M:M,"<>Sheet1!A20")-COUNTIF('FA 08-20-10'!M:M,"<>Sheet1!A21")-COUNTIF('FA 08-20-10'!M:M,"<>Sheet1!A22"), IF(AND(OR('FA 08-20-10'!$B:$B={"FTIC","TRANS"}),'FA 08-20-10'!E2="STU"))

Thanks in advance for your help. This is the most helpful forum around!


hi,
i have 1 column of alpha-numeric data with say 6 items in a row having one number then 5 items in a row with another number. i wish to create a formula to start counting from 1 up to say 6 (or however many cells have the same number) then begin the count again from 1 when it recognises a new number.
this is kinda complicated and very hard to explain. i'll try give an example

A
1| 200-PU-01
2| 200-PU-10
3| 200-PU-12
4| 200-PU-15
5| 211-PU-01
6| 211-PU-10
7| 211-PU-12

i wish to have a formula on column B which begins counting the 200 series, then recognises the 211 series and begins counting again. e.g.

A
1| 200-PU-01
2| 200-PU-10
3| 200-PU-12
4| 200-PU-15
5| 211-PU-01
6| 211-PU-10
7| 211-PU-12

B
1| 01
2| 02
3| 03
4| 04
5| 01
6| 02
7| 03


i managed to find a formula which does slightly the same thing on another job however its far to complicated for me to figure out.

=IF(A3="","",IF(COUNTIF($A$3:A3,("*"&(MID(A3,9,2)&"*")))<10,MID(A3,9,2)&" - "&"0"&COUNTIF($A$3:A3,("*"&(MID(A3,9,2)&"*"))),MID(A3,9,2)&" - "&COUNTIF($A$3:A3,("*"&(MID(A3,9,2)&"*")))))

Cheers


Hi,

I am trying to be more efficient with my countif functions. I have 5 regions on a sheet that i want a formula to count how many times 'UNVAILABLE' and 'VACANT' are in the 5 regions, then subtract that number from 39. So the formula i have at the moment is

Code:

=39-(COUNTIF(B8:B14,"VACANT"))-(COUNTIF(B16:B25,"VACANT"))-(COUNTIF(I8:I14,"VACANT"))-(COUNTIF(I16:I24,"VACANT"))-(COUNTIF(P8:P13,"VACANT"))-(COUNTIF(B8:B14,"UNAVAILABLE"))-(COUNTIF(B16:B25,"UNAVAILABLE"))-(COUNTIF(I8:I14,"UNAVAILABLE"))-(COUNTIF(I16:I24,"UNAVAILABLE"))-(COUNTIF(P8:P13,"UNAVAILABLE"))


Can anyone help top condense the forumla?

Thanks,
Mark


Hi, Just wanted to know if the formula below can be shortened? I need to do it 3 more times for 3 or 4 more rows... Thanks for any help on this.

=COUNTIF(G18,">""")+COUNTIF(L18,">""")+COUNTIF(Q18,">""")+COUNTIF(V18,">""")+COUNTIF(AA18,">""")+COU NTIF(AF18,">""")+COUNTIF(AK18,">""")+COUNTIF(AP18,">""")+COUNTIF(AU18,">""")+COUNTIF(AZ18,">""")+COU NTIF(BE18,">""")+COUNTIF(BJ18,">""")+COUNTIF(BO18,">""")

I do know that the other rows will change the number 18 value (Row), so this is why I can't just paste and go, I would have to manually change the 18 value for the new rows... Thanks.


Hi
I got the following formula, that checks three columns for duplicates at the same time and turn the cell red if true. This is basically for room occupancy Room, date, time.
However the formula is too big and excel do not let me put it in the conditional formatting formula field.
Can anybody help me how to build a macro which do the same?
The formula is as follows:
Code:

=OR(IF(&D4="Room1",AND((COUNTIF(D:D,$D4)>9),(COUNTIF(E:E,$E4)>9),(COUNTIF(F:F,$F4)>9))), IF(&D4="Room2",AND((COUNTIF(D:D,$D4)>8),(COUNTIF(E:E,$E4)>8),(COUNTIF(F:F,$F4)>8))),IF(&D4="Room3",AND((COUNTIF(D:D,$D4)>10),(COUNTIF(E:E,$E4)>10),(COUNTIF(F:F,$F4)>10))),IF(&D4="Room4",AND((COUNTIF(D:D,$D4)>8),(COUNTIF(E:E,$E4)>8),(COUNTIF(F:F,$F4)>8))),IF(&D4="Room5",AND((COUNTIF(D:D,$D4)>5),(COUNTIF(E:E,$E4)>5),(COUNTIF(F:F,$F4)>5))),IF(&D4="Room6",AND((COUNTIF(D:D,$D4)>10),(COUNTIF(E:E,$E4)>10),(COUNTIF(F:F,$F4)>10))),IF(&D4="Room7",AND((COUNTIF(D:D,$D4)>10),(COUNTIF(E:E,$E4)>10),(COUNTIF(F:F,$F4)>10))),IF(&D4="Room8",AND((COUNTIF(D:D,$D4)>5),(COUNTIF(E:E,$E4)>5),(COUNTIF(F:F,$F4)>5))),IF(&D4="Room10",AND((COUNTIF(D:D,$D4)>6),(COUNTIF(E:E,$E4)>6),(COUNTIF(F:F,$F4)>6))),IF(&D4="Room11",AND((COUNTIF(D:D,$D4)>4),(COUNTIF(E:E,$E4)>4),(COUNTIF(F:F,$F4)>4))),IF(&D4="Room12",AND((COUNTIF(D:D,$D4)>6),(COUNTIF(E:E,$E4)>6),(COUNTIF(F:F,$F4)>6))))


Thanks


Data array is 720 cells (90*6) containing integers between 1 and 49, is up
to 90 user selections of 6 different numbers in each row.
COUNTIF (repeated 49 times) returns the count of each of the 49 numbers,
into a report - no problems.

I'd now like to "cream off" the top 2 "counts", report the actual numbers,
and the number of times selected
Also, "cream off" the lowest counts, ditto.

there could be more than one number with an equal count, in both instances.

EXAMPLE the numbers "5" and "34" selected 17 times
the number "13" selected 15 times
(in practice, could be a dozen or so)
and The number " 35" selected 3 times
The number "17" selected 4 times
Is this possible, if so, how?





Hello,

I have many number values in different non-contiguous cells, I have to count the cells having value >1 only.

As the cells are not adjacent, the regular formula does not work.

I tried the following and got the desired result :

=(COUNTIF(A4,">1")+COUNTIF(C4,">1")+COUNTIF(E4,">1")+COUNTIF(H4,">1")+COUNTIF(I4,">1")+COUNTIF(K4,"> 1")+COUNTIF(M4,">1")+COUNTIF(N4,">1")+COUNTIF(O4,">1"))

But By this way, Every time I will need to enter this big formula.

Is there any way to Shorten this formula? or Can you suggest any other solution?

Basically My Query is :

A1 : 3
B1 : 0
C1 : 1
d1 : 2
e1 : 0
f1 : 5
I have to count the cells having value ">1", i.g. (in current case) countif(A1, D1, f1,">1")

Please Suggest.


I am attempting to count the number of times the last entry in a row occurs.

You can see what I've done, but the bottom 2 are incorrect. It should only count if they are in sequence from the end.

Row 6 should be 1 ... row 7 should be 3

Thanks !

A B C D E F 1 Fri Sat Sun Mon Tue # of Times The Last # is repeated 2 12 12 12 9 9 2 3 2 2 2 2 2 5 4 25 25 23 23 23 3 5 9 8 8 8 8 4 6 8 8 14 14 8 3 7 11 14 11 11 11 4
Spreadsheet Formulas Cell Formula F2 =COUNTIF(A2:E2,E2) F3 =COUNTIF(A3:E3,E3) F4 =COUNTIF(A4:E4,E4) F5 =COUNTIF(A5:E5,E5) F6 =COUNTIF(A6:E6,E6) F7 =COUNTIF(A7:E7,E7)


Is it possible to count the number of times a value appears in a range using a cell ref as the criteria, but the value will only be part of a string in the cells in the range?

in other words:

range values:
A , B
"1 v 2" , "11 v 1"
"3 v 4" , "12 v 3"
"1 v 3" , "1 v 13"
"2 v 4" , "12 v 11"

I want to count how many times "1" appears in the range? And 2, and 3, etc... so I can generate a list like this:

C,D
1,4
2,2
3,3
4,2
11,2
12,2
13,1

Experimenting, I came up with a very non-elegant solution that kinda works, but I'm hoping there's a better way:

C,D
1,=countif($A$1:$B$4,"1 v*")+countif($A$1:$B$4,"*v 1")
2,=countif($A$1:$B$4,"2 v*")+countif($A$1:$B$4,"*v 2")

and so on...

I'd prefer to have something more like:

1 =countif(<range>,"<the part I can't figure out with wildcards>" & C1)

in other words, how do I say, "count if the value of my reference cell is contained within the text of a cell in the range" ??

I have both 2003 and 2007 available. Not sure if this is possible, but I'd appreciate the help!

TIA


I am using the countif function to analyis the number of times certain numbers occur. Is there a way to have Excel highlight the cell using the countif function when the result is "0" for example.

That is, say the data A1:E2 being analyzed is as follows:
1 3 9 5 20
5 4 2 5 5

A4 uses the countif command to check for 7s. So, in addition to a "0" being displayed in A4, I want A4 to be highlighted so I can notice it immediately. That is, in actuality, I am viewing a much larger set of data and wish to notice the numbers with "0" hits right away.

Any ideas?

Thanks in advance.


I have a large database containing several columns of information such as
store number, store area and general ledger account number.
I'd like to come up with a formula on a new Excel sheet where I can count
the number of stores that meet a certain critera....For example...I'd like
for it to count the number of stores in area 11 that are also in GL account
01-101.
How can I create a formula that will count this information only if the data
meets the critera I need? I've tried Countif(Column of Area
data,"=11")and(countif(column of GL data, "=01-101")), however I keep getting
an error and can't seem to come up with the right way to do this.



Hello All,

I need to count the number of times a phone number in a list has been called by unique callers phone number.

In the spread sheet attached, sheet "Volume of Calls" Cell B3 is where the formula needs to go.

The formula needs to look at sheet "All Calls" column E for the text that is displayed in "Volume of Calls" A3 and then work out how many unique callers phone numbers have called it by searching for unique numbers in "All Calls" column D

Hope that makes sence!!

All phone numbers in the lists are made up for test only.


Hi,

This formula works up to the COUNTIF(D10:D10,"na") item but does not work after I put in the final COUNTIF(D18:D18,"na") ???

If there a limitation on the number of SUMs and/or COUNTIF's that I can have?

Thanks,

Shane.

=SUM(COUNTIF(D2:D2,"y")+SUM(COUNTIF(D5:D5,"y")+SUM(COUNTIF(D10:D10,"y")+SUM(COUNTIF(D18:D18,"y")+SUM (COUNTIF(D2:D2,"na")+SUM(COUNTIF(D5:D5,"na")+SUM(COUNTIF(D10:D10,"na")+SUM(COUNTIF(D18:D18,"na"))))) ))))


Hello Everyone,
Using Excel 2007.

I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case).
I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:

Code:

=COUNTIF(A1:A10,2)+COUNTIF(C1:C10,2)+COUNTIF(E1:E10,2)+COUNTIF(G1:G10,2)+ etc up and including (O1:O10,2)


.

Is there any other way to make a shorter formula to count the number of 2's in every other column?
Thanks for any help,
Mike


Hi,

can you please help me to put a formula to count the number of entires in columns.

Column A to Column Z (week numbers 1-26)
ColA colB ColC ColD ColE etc...
wk1 Wk2 Wk3 Wk4 Wk5 etc...
#12 #11 #12 #13 #15
#14 #14 #14 ..... #14

from row 2 to 1000 have some data.
i want to put a formula in Column 27 to count how many times a particular data is appeared. eg if i want to know how many times "#12" appeared in column A- Z i can easily put countif like =COUNTIF(A2:Z2,"#12") which will give the number of times the value "#12" appreared, but now i want to know how many times it appreaed continesouly.

eg:
if i want to look for "#14" and if i use countif it would give me 4 as as answer but i want the result as 3 as the entry stops in colD(it will always be blank I had put ...)

All i want to get the number of times that the data appeared continueously

thanks for your help


Hi all,

I am trying to count the number of occurences in "column A10 to A20" where the number exceeds a specific value in cell A1.

I thought the solution would be the formula:-

=COUNTIF(A10:A20,>A1)

..however I just keep getting an error messsage, although the basic formula does work wilhout the ">", but this only counts the exact matches.....am I being a bit dull?


Thanks,

Stu


Hi, I've used this site countless times and been helped tremendously over in the past, but this is my first post. I need to generate text based on whether a "lookup" cell partially matches a range of other cells. I understand how to do a partial match ("*"& A1 &"*") but I need to check a range of cells for a partial match. I had hoped ("*"& A1:G1 &"*") would work, but it doesn't. I've tried match, countif, search, hlookup, lookup, making the formula an array and even Macros with no luck. I'm currently getting by with the following formula but as you will see it is very long and when copied into further cells it takes up a lot of space.

=IF(COUNTIF(W$3,"*"&$E12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$F12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$G12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$H12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$I12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$J12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$K12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$L12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$M12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$N12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$O12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$P12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$Q12&"*")>0,"N",IF(COUNTIF(W$3,"*"&$R12&"*")>0,"N","Complete"))))))))))))))

I'd like to use something like - IF(COUNTIF(W$3,"*"&$E12:$R12&"*")>0,"N","Complete")

Any help would be most appreciated. Thank you

Hi,

I have a worksheet that works pretty well thanks to some great help. But I want to use vba to create the same result, mainly so further error checking and notification to users can be done. the following formula has been used so that no duplicate numbers can be entered and that only specific numbers can be entered which are contained in another sheet.

Code:

=IF(OR(AND(C3<>"",COUNTIF(Buses!A$2:F$500,C3)<>1),AND(D3<>"",COUNTIF(Buses!A$2:F$500,D3)<>1),AND(E3<>"",COUNTIF(Buses!A$2:F$500,E3)<>1),AND(F3<>"",COUNTIF(Buses!A$2:F$500,F3)<>1),COUNTIF($A$3:$F$46,C3)>1,COUNTIF($A$3:$F$46,D3)>1,COUNTIF($A$3:$F$46,E3)>1,COUNTIF($A$3:$F$46,F3)>1),"E",ROW()-2)


Any assistance would be appreciated.

Regards

David


Hi, what am I doing wrong with the following formula:

=countif(sheet1:sheet31!E6:E35,">=1")

FOR some reason this formula fails when I use the (sheet1:sheet31) with the column range (E6:E35).

Is it possible to include a multiple sheet count and column range in the same Formula? Thanks for any helpful responses..

By the way, the formula works ok just as long as I'm not trying to read multiple sheets.. Please help on this.

This is the formula I had to write for each 30 rows X 31 days because I cant get the (sheet:sheet) to work:

=COUNTIF('4TH'!E6:E35,E6)+COUNTIF('2ND'!E6:E35,E6)+COUNTIF('3RD'!E6:E35,E6)+COUNTIF('5TH'!E6:E35,E6) +COUNTIF('6TH'!E6:E35,E6)+COUNTIF('7TH'!E6:E35,E6)+COUNTIF('8TH'!E6:E35,E6)+COUNTIF('9TH'!E6:E35,E6) +COUNTIF('10TH'!E6:E35,E6)+COUNTIF('11TH'!E6:E35,E6)+COUNTIF('12TH'!E6:E35,E6)+COUNTIF('13TH'!E6:E35 ,E6)+COUNTIF('14TH'!E6:E35,E6)+COUNTIF('15TH'!E6:E35,E6)+COUNTIF('16TH'!E6:E35,E6)+COUNTIF('17TH'!E6 :E35,E6)+COUNTIF('18TH'!E6:E35,E6)+COUNTIF('19TH'!E6:E35,E6)+COUNTIF('20TH'!E6:E35,E6)+COUNTIF('21ST '!E6:E35,E6)+COUNTIF('22ND'!E6:E35,E6)+COUNTIF('23RD'!E6:E35,E6)+COUNTIF('24TH'!E6:E35,E6)+COUNTIF(' 25TH'!E6:E35,E6)+COUNTIF('26TH'!E6:E35,E6)+COUNTIF('27TH'!E6:E35,E6)+COUNTIF('28TH'!E6:E35,E6)+COUNT IF('29TH'!E6:E35,E6)+COUNTIF('30TH'!E6:E35,E6)+COUNTIF('31ST'!E6:E35,E6)


I perform analysis on operating room utilization. Normally I'm provided the breakdown in an excel sheet with the case count already established at various times of the day. This time I received paper data that I need to create a table in excel and then determine the frequency of cases starting after 3:30, after 4:30, after 5:30 and so forth.

I thought I could use a countif formula but it only returns #NAME?.

The formula was =COUNTIF(D5:D47,"<=D6") Column D consists of start times formated as h:mm AM/PM because the record data was in military time. However, I did try the formula before reformatting but the answer was the same error.

Either I have the wrong formula or the wrong format in the answer cell which is formatted to return number.

Anyone have an answer to this quandary?

Thank you,
Ang

How do I run a countif formula to count the number of cells in a column where the number contained therein is between a set of values

so how would I do this for example:

=countif(a:a,values between 0 and 3)

???

Thanks!