Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
Is there a way to use the Count if function to count if a value is between 25
and 40? Using Excel 2003 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
Do you mean =25 and <=40 ?
Try this: =COUNTIF(A1:A20,"=25")-COUNTIF(A1:A20,"40") -- Biff Microsoft Excel MVP "benny" wrote in message ... Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
What do you mean by "between"? If the 25 and 40 are to be excluded...
=COUNTIF(A1,"<40")-COUNTIF(A1,"<=25") If they are to be included... =COUNTIF(A1,"<=40")-COUNTIF(A1,"<25") Note the different "<", "<=", "" and "=". Rick "benny" wrote in message ... Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
Assuming you are including 25 and 40
=SUM(COUNTIF(A1:A100,{"=25","40"})*{1,-1}) "benny" wrote: Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
To test only one cell:
=AND(COUNTIF(A1,{"=25","<=40"})) or =COUNTIF(A1,"=25")*COUNTIF(A1,"<=40") To test a range of cells: =SUM(COUNTIF(A1:A25,{"<25","<=40"})*{-1,1}) or =COUNTIF(A1:A25,"<=40")-COUNTIF(A1:A25,"<25") Is that something you can work with? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "benny" wrote in message ... Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
For something that's a little more eccentric:
=INDEX(FREQUENCY(A1:A20,{25,40}-{1,0}),2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Do you mean =25 and <=40 ? Try this: =COUNTIF(A1:A20,"=25")-COUNTIF(A1:A20,"40") -- Biff Microsoft Excel MVP "benny" wrote in message ... Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
"T. Valko" wrote in message
... For something that's a little more eccentric: =INDEX(FREQUENCY(A1:A20,{25,40}-{1,0}),2) Very clever, well done. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
Function CountBetw(iRange As Range, lowNum, hiNum, Optional inclLow =
_ True, Optional inclHi = True) If inclLow = True And inclHi = True Then CountBetw = Application.CountIf(iRange, "=" & lowNum) - _ Application.CountIf(iRange, "" & hiNum) ElseIf inclLow = False And inclHi = False Then CountBetw = Application.CountIf(iRange, "" & lowNum) - _ Application.CountIf(iRange, "=" & hiNum) ElseIf inclLow = True And inclHi = False Then CountBetw = Application.CountIf(iRange, "=" & lowNum) - _ Application.CountIf(iRange, "=" & hiNum) ElseIf inclLow = False And inclHi = True Then CountBetw = Application.CountIf(iRange, "" & lowNum) - _ Application.CountIf(iRange, "" & hiNum) End If End Function Alan Beban Rick Rothstein (MVP - VB) wrote: What do you mean by "between"? If the 25 and 40 are to be excluded... =COUNTIF(A1,"<40")-COUNTIF(A1,"<=25") If they are to be included... =COUNTIF(A1,"<=40")-COUNTIF(A1,"<25") Note the different "<", "<=", "" and "=". Rick "benny" wrote in message ... Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
"Sandy Mann" wrote in message
... "T. Valko" wrote in message ... For something that's a little more eccentric: =INDEX(FREQUENCY(A1:A20,{25,40}-{1,0}),2) Very clever, well done. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk I think it was one of Ron Coderre's posts where I first saw that technique. -- Biff Microsoft Excel MVP |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
I think it was one of Ron Coderre's posts where I first saw that
technique. -- Biff Microsoft Excel MVP Well done Ron then. I sometimes think that finding the origin of formulas is like when my kids were small and one would start crying: Me: "Why are you crying?" 1st Child: "Because she kicked me!" Me: "Why did you kick her?" 2nd Child: "Becaue she hit me!" Me: "Why did you hit her?" 1st Child: "Because she called me names" Me: Why did ........ " I never did get to the bottom of things. <g -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If Function
Yes include the 25 and 40. All great solutions. I'm fixed now. Thanks so
much. "benny" wrote: Is there a way to use the Count if function to count if a value is between 25 and 40? Using Excel 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count function | Excel Worksheet Functions | |||
i think i need count function help | Excel Worksheet Functions | |||
COUNT IF FUNCTION | Excel Worksheet Functions | |||
Count Function | Excel Worksheet Functions | |||
Count If Function | Excel Worksheet Functions |