Search code examples
excelexcel-formulaexcel-2010

Create a formula to find the count in Excel column

I not very use to use excel complex formula. Here is request Column has few number (-ve/+ve). I have to count these based on intervals. These interval are not pre-decided. See screen Shot Example

Values in Label col can change in run time. A is less than -15, B is between -15 to 6 and so on. I have to create a formula to add a count in the Count col.

Please guide

Thank you Regards

Solution

  • You will save yourself a lot of maintenance headaches by re-formatting the "Labels" Table:

    D E F G
    6 Greater Than or Equal To Less Than or Equal To Count
    7 Label A -99 -16 (formula below goes here)
    8 Label B -15 -6
    9 Label C -5 5
    10 Label D 6 15

    The formula to place in the first count cell (G7 in my example) is:

    =COUNTIFS(A$1:A$19,">="&E7,A$1:A$19,"<="&F7)

    And then fill it down the length of the table. (In this example 4 rows). Be mindful of the $ that lock the rows of your values column.