| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
syntax for countif when cells fall within a numerical range
I have a column of numerical data, for which I need frequency counts within
certain ranges. E.G. If a cell value is = 41 AND <= 50, I want to count that cell as one. I can't figure out the syntax. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
syntax for countif when cells fall within a numerical range
Countif can only be used with one criteria.
Use Sumproduct instead. For example, if your list is in A1:A100, you could use =SUMPRODUCT(--(A1:A100=41),--(A1:A100<=50)) "Tom L" wrote: I have a column of numerical data, for which I need frequency counts within certain ranges. E.G. If a cell value is = 41 AND <= 50, I want to count that cell as one. I can't figure out the syntax. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
syntax for countif when cells fall within a numerical range
Countif can only be used with one criteria.
Think we could use 2 countifs as another option: =COUNTIF(A:A,"=41")-COUNTIF(A:A,"50") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
syntax for countif when cells fall within a numerical range
Ha, very cute. It's not really 'one' countif, but I like it anyways.
"Max" wrote: Countif can only be used with one criteria. Think we could use 2 countifs as another option: =COUNTIF(A:A,"=41")-COUNTIF(A:A,"50") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
syntax for countif when cells fall within a numerical range
Countif can only be used with one criteria.
But you can use more criteria when nested inside another function like this: =SUM(COUNTIF(A1:A20,{"=41","50"})*{1,-1}) =SUM(COUNTIF(A1:A20,{"X","Y","Z"})) G1:G3 = X, Y, Z =SUMPRODUCT(COUNTIF(A1:A20,G1:G3)) -- Biff Microsoft Excel MVP "sb1920alk" wrote in message ... Countif can only be used with one criteria. Use Sumproduct instead. For example, if your list is in A1:A100, you could use =SUMPRODUCT(--(A1:A100=41),--(A1:A100<=50)) "Tom L" wrote: I have a column of numerical data, for which I need frequency counts within certain ranges. E.G. If a cell value is = 41 AND <= 50, I want to count that cell as one. I can't figure out the syntax. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
syntax for countif when cells fall within a numerical range
"Tom L" wrote:
I have a column of numerical data, for which I need frequency counts within certain ranges. E.G. If a cell value is = 41 AND <= 50, I want to count that cell as one. I can't figure out the syntax. One way, via sumproduct, eg: =SUMPRODUCT((A2:A10=41)*(A2:A10<=50)) Adapt the range to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Highlight data that doesn't fall within certaint range | Excel Worksheet Functions | |||
| Adding amounts that fall within a range | Excel Worksheet Functions | |||
| Counting if numbers fall within a specified range | Excel Discussion (Misc queries) | |||
| How to total itmes if they fall between a date range | Excel Worksheet Functions | |||
| range and cells syntax | Excel Worksheet Functions | |||
Hybrid Mode
