Any suggestions about the "countif" function?

The COUNTIF function uses a number for setting the condition, for example 
=COUNTIF(A1:A100,"<10"). How is it possible to replace the condition (in 
this case "<10") with a cell address?
Thank you very much.
Gene


0
9/21/2007 3:16:07 PM
excel 39880 articles. 2 followers. Follow

6 Replies
255 Views

Similar Articles

[PageSpeed] 15

If the condition is in cell D1, then try this:

=COUNTIF(A1:A100,"<"&D1)

Hope this helps.

Pete

On Sep 21, 4:16 pm, "EpsilonRho" <Epsilon...@nospam.ooo> wrote:
> The COUNTIF function uses a number for setting the condition, for example
> =COUNTIF(A1:A100,"<10"). How is it possible to replace the condition (in
> this case "<10") with a cell address?
> Thank you very much.
> Gene


0
pashurst (2576)
9/21/2007 3:19:25 PM
Thank you very much, Pete. It works.
Gene

"Pete_UK" <pashurst@auditel.net> wrote in message 
news:1190387965.804044.202640@19g2000hsx.googlegroups.com...
If the condition is in cell D1, then try this:

=COUNTIF(A1:A100,"<"&D1)

Hope this helps.

Pete

On Sep 21, 4:16 pm, "EpsilonRho" <Epsilon...@nospam.ooo> wrote:
> The COUNTIF function uses a number for setting the condition, for example
> =COUNTIF(A1:A100,"<10"). How is it possible to replace the condition (in
> this case "<10") with a cell address?
> Thank you very much.
> Gene



0
9/21/2007 3:44:37 PM
You're welcome, Gene - thanks for feeding back.

Pete

On Sep 21, 4:44 pm, "EpsilonRho" <Epsilon...@nospam.ooo> wrote:
> Thank you very much, Pete. It works.
> Gene
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:1190387965.804044.202640@19g2000hsx.googlegroups.com...
> If the condition is in cell D1, then try this:
>
> =COUNTIF(A1:A100,"<"&D1)
>
> Hope this helps.
>
> Pete
>
> On Sep 21, 4:16 pm, "EpsilonRho" <Epsilon...@nospam.ooo> wrote:
>
>
>
> > The COUNTIF function uses a number for setting the condition, for example
> > =COUNTIF(A1:A100,"<10"). How is it possible to replace the condition (in
> > this case "<10") with a cell address?
> > Thank you very much.
> > Gene- Hide quoted text -
>
> - Show quoted text -


0
pashurst (2576)
9/21/2007 4:00:40 PM
In microsoft.public.excel on Fri, 21 Sep 2007, Pete_UK 
<pashurst@auditel.net> wrote :

>If the condition is in cell D1, then try this:
>
>=COUNTIF(A1:A100,"<"&D1)
>
>Hope this helps.

That's useful - thanks.
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
9/21/2007 5:23:54 PM
Hey, thanks for the feedback, Paul.

Pete

On Sep 21, 6:23 pm, Paul Hyett <vidcap...@invalid83261.co.uk> wrote:
>
> That's useful - thanks.
> --
> Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)


0
pashurst (2576)
9/21/2007 11:46:09 PM
In microsoft.public.excel on Fri, 21 Sep 2007, Pete_UK 
<pashurst@auditel.net> wrote :

>Hey, thanks for the feedback, Paul.
>
>Pete
>
I have lots of spreadsheets containing temperature data, and being able 
to point 'countif' to a single cell will make it a lot easier to count 
occurrences of temperatures within a given range, than caving to alter 
loads of formulas :)
-- 
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)
0
vidcapper (121)
9/22/2007 7:59:00 AM
Reply:

Similar Artilces:

CountIF #17
I have a list of records where the Project Manager has several numbers assigned to him but sometimes the number is duplicated. How do I use the CountIF so that it only counts the number once? Here is an example of my data: Sales Order Project Mgr 6100517416 Gomes 6100517416 Gomes 6100517424 Gomes 6100517424 Gomes 6100517425 Gomes 6100517425 Gomes 6100521493 DeVivo 6100531944 Sturm 6100531944 Sturm Hi Elizabeth if you want a count per Project Manager use the following formula =SUMPRODUCT(--($B$11:$B$19=F11)/(COUNTIF($A$11:$A$19,$A$11:$A$19)+($A$11:$A$19=""))) ...

Countif's and And functions
I need a formula that will take: Countif(a2:a7="EA" AND b2:B7="set") Obviously this isn't a valid formula but trying to accomplish this. Found my answer is another persons question/reply: =SUMPRODUCT(--(A2:A100="S"),--(B2:B100="E")) so the answer is SP :-) -- HTH RP (remove nothere from the email address if mailing direct) "JoatNIC" <JoatNIC@discussions.microsoft.com> wrote in message news:622CFA2D-A1C4-4B72-9209-0ABB794A1927@microsoft.com... > I need to count the number of cells in Column A that contain an S, AND t...

COUNTIF for 2 columns
I'm trying to set a parameter of the following in a formula in column J: =COUNTIF($C:$C,$B300) That also only counts the number of times in column J where a cells (J1:J299) value is greater than "0". ----this is where I can't figure out the how to?? In other words, I need to set-up a formula in column J row 300 that I want to count the number of times that column C is equal to cell B300 only if the value in column J's cell is greater than zero. Thanks, Bill Try =SUMPRODUCT(--($J$1:$J$299>0),--($C$1:$C$299=$B300)) -- HTH Bob "b1...

Vlookup, COUNTIF, IF or Nested
Hello, I have a work sheet "Raw Data" of about 1500 rows and 30 Columns. In columns K, K, L, M, T and W are names of staff. In worksheet "Lookups" I have a defined list of "Leavers". I need to return the names of the leaver/s in the last column of the "Raw Data" sheet. eg: Row 15 Column K contains "Joe Blog" Column M contains "Micky Mouse" and Column W contains "Jane Doe". in my defined list of leavers "Joe Blog" and "Jane Doe" are listed, in the last column of the "Raw Data&quo...

COUNTIF #16
I want to count how many numbers in a range are less than a given value. However, I don't want that given value to be hardcoded in the COUNTIF formula. For example: I want this formula: =COUNTIF(P4:P1073,">=-0.0045") to look something like: =COUNTIF(P4:P1073,">=V4") However that second formula doesnt seem to work. Any ideas? =COUNTIF(P4:P1073,">="&V4) -- HTH RP (remove nothere from the email address if mailing direct) "adamcal" <adamcal@discussions.microsoft.com> wrote in message news:C152B7A1-3388-422A-8383-4EC3...

COUNTIF #28
Hi, I need to count the number of FALSE values returned in another worksheet based on 'and' function in that column. I am using the following function: =COUNTIF('Main Entry Sheet'!A26:A463,"'Main Entry Sheet'!AN26:AN463=FALSE") but I get a 0 value returned as "=and........" is the true value of the cells rather than the returned "TRUE/FALSE" Please help Unclear. You want to count the cells in A26:A463 based on the condition that the corresponding cell in AN26:AN463 has FALSE value? It would seem that the answer will be exactly the s...

COUNTIF function question...
Greetings, So I have a spreadsheet where we enter customer data and then we use a COUNTIF on another sheet to count several categories (Sales Rep, type, etc). What I am trying to find out is if I can add up the sales per rep, whose names are entered in column G, but only count them if there is a certain entry in column D. Column D has 6 options, and if 2 of them are selected, I want the sales rep's numbers to be excluded from the data collection. Does this make sense? Thoughts? Suggestions? I know what I want the function to say, just don't know how I should program it: "C...

Countif that can i half
I was wondering if anyone could help with a formula I have a holidday spreadsheet that tracks employees holidays and sick throughout the year at work. I have a colum that counts the number of holidays ane employee taske but I am not sure how to count half days can any one help my formula is below =COUNTIF(M6:GL6,"Bh")+(COUNTIF(M23:GK23,"Bh"))=COUNTIF(M6:GL6,"Bh")/0.05 thanks -- N/A You say you have a column but you reference M6:GL6 Please tell us the exact layout of the worksheet: employees name in A2 holidays taken in M2:GL2 using the code Bh and half h...

Countifs formula not calculating my specific data
I am working with a Countifs formula, and it is not returning the correct information when I am using the specific data I want, but if I simplify it, it will return what I want. i.e. =countifs(A3:A500,"1/?",B3:B500,"DAYTONA",C3:C500,"SMITH, JOHN") ^THIS IS NOT WORKING, BUT...... =COUNTIFS(A3:A500,"X",B3:B500,"Y",C3:C500,"Z") does work, why is it doing this? Would truley appreciate any help. >=countifs(A3:A500,"1/?",B3:B500,"DAYTONA",C3:C500,"SMITH, JOHN") >^THIS IS NOT WORKING...

Countif in two columns and in different cells
I'm not sure which function I need to add text "p" and "cps" when this data is in different columns. The total in this ex: would be p+cps = 2. Any help would be greatly appreciated. I'm new at this. shift class p cps p x p x p x p cps total p+cps = 2 "ferde" wrote: > I'm not sure which function I need to add text "p" and "cps" when this > data is in different columns. The total in this ex: would be p+cps = 2. > Any help would be gre...

nesting with countif
Hi, I am having trouble with nesting the and command in a sheet I am attmepting to prepare a spreadsheet to keep track of the clients in the various programs at the not-for-profit blind services agency where I work. I pasted a small piece of the sheet below. I used the countif function in the formulas in column R below =COUNTIF($S$25:$S$31,"Alp II", =COUNTIF($S$25:$S$31,"Alp III", =COUNTIF($S$25:$S$31,"Alp II E", and =COUNTIF($S$25:$S$31,"Ancillary to add up the how many people were in each program, but I have not been able to figure out how to count for ...

COUNTIF NAME RANGE
I have one worksheet called with column A1:A72 filled with 6 digit numbers in each cell - i.e. 239260 (A1), 239060 (A2), etc. all the way down to A72 with 6 digit numbers. I have a second worksheet called query parameters. On it is a named range called SHIPSEA which is another list of 6 digit numbers in a column that identify stations that are assigned these 6 digit numbers. The numbers in this column identify SHIPSEA. How do I use a CountIF formula to count the number of instances that SHIPSEA numbers appear in the worksheet one column (A:A)? My formula returns a zero: =COUNTIF(A1:A72,S...

Countif(table;(">"&A1)&("<"&B1))
This content works individualy, how can i make it work? is it any way to count the values between A1> and <B1? tks The simplest is =countif(range,"<"&b1)-countif(range">"&A1) "Micos3" wrote: > This content works individualy, how can i make it work? is it any way to > count the values between A1> and <B1? > > tks Try... =COUNTIF(Range,">"&A1)-COUNTIF(Range,">="&B1) If your version of Excel uses the semi-colon as a separator, replace commas with semi-colons. Hope this helps! In art...

Countif gone wrong
Hi, A B C D E 2000BC DEF DEF DEF 2000BC ABC 2000BC DEF 2000BC DEF 2000BC 2000BC 2000BC 2000BC 2000BC ABC 2000BC DEF DEF DEF This normally works but Im having a problem with it now. My formula looks like this =COUNTIF(Sheet3!A:A,"=2000BC")+COUNTIF(Sheet3!B:B,"=2000BC")+COUNTIF(Sheet3!C:C,"=2000BC")+COUNTIF(Sheet3!D:D,"=2000BC")+COUNTIF(Sheet3!E:E,"=2000BC") The column range is a lot of lines ie reason why I use column:column, the last countif was added & it returns answer as 0 instead of adding 2 more-if I change the formula...

CountIf Limitation?
I have a column that contains three text descriptions ie. Intermediate Average or Advance. Using the CountIf function , I am able to count each description separately and then show the overall total. Can I modify the Countif to add up the three descriptions and display a single total of their occurance? Thanks very much One way: =SUMPRODUCT(COUNTIF(A:A,{"Intermediate","Advance","Average"})) In article <06f001c48c09$d156fdb0$a401280a@phx.gbl>, "Deltaecho" <anonymous@discussions.microsoft.com> wrote: > I have a column that conta...

Using COUNTIF (specific cells over multiple sheets) � can anyone help?
Hi I�d be really grateful if someone could assist me in how to �formula the below: I have twelve identically formatted worksheets (�A� through to �J�) In each of the cells B6:F6, B10:F10, B14:F14, B18:F18 & B22:F22 on eac sheet there will be a different one of 5 words (each word can and wil occur more than once within the above range). For the purpose of thi exercise I need to ignore the text in the other rows (eg 7-9, 11-1 etc). What I need to tally on a separate sheet within the workbook is ho many times each of the five words occur in the group above (eg B6:F6 B10:F10, B14:F14, B1...

COUNTIF #15
Hi everyone, I have a question regarding the COUNTIF function. How do I use this function to count the number of times a record appears when I only enter a portion of the search data. eg. I have a column of data that, among other things, contains text as "anautoalert". How do I write the COUNTIF to simply count the number of times that "auto" appears? Thanks! Hi James =COUNTIF(A1:A100,"*auto*") Cheers JulieD "James" <james@hamilton.name> wrote in message news:0d5101c5364d$e06ae250$a401280a@phx.gbl... > Hi everyone, > > I hav...

Countif #12
Thanks for the help, that worked out great -- patrick s ----------------------------------------------------------------------- patrick s.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1469 View this thread: http://www.excelforum.com/showthread.php?threadid=26318 ...

Dcount or Countif?
I have a spreadsheet with 5 columns and need to count the number of responses between a range based on the criteria of two cells. For instance the range is A1:E16 Columns A = Name B = Address C = City D = State E = Age Criteria A1 = Jack D2 = Michigan Count Brackets: Age = 20-29, 30-39, 40-49, 50-59 I need an equation that will count the number of records with a name of Jack and a state of Michigan and filter that count to a range. I will drop the count for each range in a different cell so the equation would be a static age bracket. Can anymore make any suggestions? On Tue, 7 Apr 2009 ...

CountIF multiple conditions
Hi, I have been searching for some help: I have multiple columns and 2000 rows with numbers I want the to count only those instances where the number 2 appears in column A AND the number 5 also appears in columns B Thanks for the help zach =sumproduct(--(a1:a2000=2),--(b1:b2000=5)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/xld.SUMPRODUCT.htm...

Countif Function??
I would like a formula which will allow me to calculate the number of mondays in a month which are either C- closed, H- holiday. or TT- term from the following list. This will be duplcated for each month. I have tried to use: =COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and the letter. Can anyone help. Regards Kevan Date April '10 1 Thursday H 2 Friday C 3 4 5 Monday C 6 Tuesday H 7 Wednesday H 8 Thursday H 9 Friday H 10 11 12 Monday H 13 Tuesday TT 14 Wednesday TT 15 Thursday TT 16 Friday TT 17 ...

COUNTIF?? SUMIF??
I need to count the rows only if these two conditions exist in the same row. Column A must be a specific numerical value and column B must contain any text. Thanks... I assumed your range is row 1:100, change to fit your data: =SUMPRODUCT(--(A1:A100=your_numerical_value),--(ISTEXT(B1:B100))) -- Regards, Dave "Vegs" wrote: > I need to count the rows only if these two conditions exist in the same row. > Column A must be a specific numerical value and column B must contain any > text. > Thanks... ...

CountIF formula
I need to run a countif formula, with 2 conditions on two different columns, The first condition is that column D matches cell A1 (text), the second condition is that column K is greater than 0. I need it to count the number of rows that meet the given conditions. Please let me know if you can help -- ROSE2102 ------------------------------------------------------------------------ ROSE2102's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9202 View this thread: http://www.excelforum.com/showthread.php?threadid=507833 Hi, Rose. Try this: http://www.officear...

Conditional Formatting & CountIf problem
Hi, I've been trying to solve this problem for quite some time now but I can't get a straight answer. I need to create a macro which will match numbers from two lists and highlight the numbers on the master list when the macro is ran. It should look work something like this: conditional format: formula is=CountIf($A$5:$A$4500 'from all grouped sheets',A5) =1 I don't know how I would program this into a VBA macro. The main idea is A5:A4500 from all grouped sheets is compared to a5:a4500 on the master sheet. Also, not all cells would be filled so I would need to omit...

CountIF problem #8
Hi, My brain isn't switched on this morning, this may be more straight forward than I think... I'm trying to do a Countif function, which only counts those in the range which are between "0" and "-2". Lets say the range is $A$1 : $A$50, and contains values between -3.00 and 5.00. How can i write this countif formula? It doesnt work the way i'm trying it! Many thanks in advance JJJ -- jjj ------------------------------------------------------------------------ jjj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7424 View th...