| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting a specific range of values within a column
Hi,
I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Simplest is to do:
=countif(A2:A200,=101)-countif(A2:A200,140) "kenm" wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Try this:
=COUNTIF(A2:A200,=101)-COUNTIF(A2:A200,140) Does that help? *********** Regards, Ron XL2002, WinXP "kenm" wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Just a typo (or two):
=COUNTIF(A2:A200,"=101")-COUNTIF(A2:A200,"140") or =COUNTIF(A2:A200,"="&101)-COUNTIF(A2:A200,""&140) kenm wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Thanks, Dave....
I deserve a newspaper over the snout for missing such an obvious error in my post. : | *********** Regards, Ron XL2002, WinXP "Dave Peterson" wrote: Just a typo (or two): =COUNTIF(A2:A200,"=101")-COUNTIF(A2:A200,"140") or =COUNTIF(A2:A200,"="&101)-COUNTIF(A2:A200,""&140) kenm wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Hi,
Another way: =SUMPRODUCT((A2:A200=101)*(A2:A200<=140)) Cheers! Jean-Guy "kenm" wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Ron,
This worked perfectly! Thanks for the assistance. -- kenm "Ron Coderre" wrote: Try this: =COUNTIF(A2:A200,=101)-COUNTIF(A2:A200,140) Does that help? *********** Regards, Ron XL2002, WinXP "kenm" wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting a specific range of values within a column
Sean,
Both you and Ron had this one nailed. Really appreciated the very quick response and your excellent help. _kenm -- kenm "Sean Timmons" wrote: Simplest is to do: =countif(A2:A200,=101)-countif(A2:A200,140) "kenm" wrote: Hi, I'm trying to count a specific range of values that occurs between two number in column. I tried some countif and IF functions but couldn't land the right formula. Any help is really appreciated. Here's my example: In a single column from A2:A200 there's a set of numbers with values ranging between 1-200. I'm trying to find a 'count' of all numbers in this column that fall 'between' 101-140. thanks for your assistance, _kenm -- kenm |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 Display value based on values in another column range | Excel Worksheet Functions | |||
| macro | Excel Discussion (Misc queries) | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
| Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
Linear Mode
