Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing values in a column (2000/SR-1)

    I have a column of numbers between .25 and 100. At the end of the column I would like to have a count of all the numbers in the column between 1 and 75. Is there a formula I can use that will give me this count?

    I know I can create a separate column that returns either true or false depending on the value of the cells in question, then count just the true values. But I want to work with a single cell at the end of each column.

    Any suggestions?
    Thanks,
    Caroline in lala-land

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    If you really mean between (not including 1 and 75), then the following array formula (hold down Ctrl and Shift when you press Enter) will do what you want:

    <pre>=SUM((A1:A17>1)*(A1:A17<75))
    </pre>


    If you want to include ones and 75s, then use this formula:

    <pre>=SUM((A1:A17>=1)*(A1:A17<=75))
    </pre>



    Change the range A1:A17 to the range where your values are located.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    Lets say that column A houses the numbers of interest, E1 the lowest criterion (your .75), and E2 the highest criterion (your 1).

    =COUNTIF(A:A,">"&E1)-COUNTIF(A:A,">="&E2)

    will give you a count of numbers in A which are > .75 and < 1, thus excluding the numbers that are equal to the criteria. A bit tweaking will allow you to modify the formula for counting that is based on a different form of between.

    This formula is "cheaper" than an equivalent array or SUMPRODUCT formula which are definitely needed when you want a multiconditional count or sum.

    Aladin
    Microsoft MVP - Excel

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    Can you tell me why this formula works? I tested it and it's perfect!

    (BTW, I was looking for a "between" statement).
    Thanks,
    Caroline in lala-land

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    I populated cells A1 thru A34 with values ranging from .75 to 100. Using your formula, I came up with a count of 8 when there are really 24 cells that meet the criteria I'm looking for. I was looking for a formula that would count all numbers falling between the ranges mentioned.
    Thanks,
    Caroline in lala-land

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    It works because the a logical True from a comparison is represented by a value of 1 and False is represented by 0. Therefore, whenever either or both comparisons is False, one or both of the values will be 0 and the product will be 0. Only when both conditions are a True will both results be 1 and the product will be 1. The array formula creates an array of ones and zeros for each comparison, and the product of those two arrays is an array of ones when both comparisons are True and zero when either or both are False. The SUM function adds up those ones and zeros to get the count of then cells that meet both conditions.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    The rule of applying COUNTIF when we have a between condition may appear confusing. It just requires some exercise. As I tried to state in my first reply, a multiconditional count or sum is ordinarily computed by means of an array formula or a SUMPRODUCT formula that is capable of operating on arrays (By the way, PivotTables etc can also be used). As a side note, array formulas are more generic than the SUMPRODUCT formulas. The former requires the key combination control+shift+enter, while the latter is entered as an ordinary formula.

    COUNTIF and SUMIF, by design, cannot work with more than one condition/criteria. With a "between" condition (which boils down to multiple conditions), COUNTIF can still be used for counting (and SUMIF for summing/totaling).

    Now back to your question.

    Ambiguity of between (as Legare also noted):

    Lets say that E1 houses the lowest and E2 the highest of criteria.

    What do we mean when we say "between E1 and E2"? It can be one of the following:

    > E1 and < E2 [ also called Exclusive Between ]
    >= E1 and <= E2 [ alsocalled Inclusive Between ]
    >E1 and <= E2
    >= E1 and < E2

    If you wanted Inclusive Between to apply, the COUNTIF formula will become:

    =COUNTIF(A:A,">="&E1)-COUNTIF(A:A,">"&E2)

    Note. Column A should not have anything else (any other numbers) but the numbers of interest that we want count.

    How this formula works?

    The first COUNTIF counts every cell in A that is greater than or equal to the value in E1. The second COUNTIF counts every cell in A that is greater than the value in E2. The latter count is then subtracted from the former count, producing the desired result.

    The array and SUMPRODUCT versions are -- these formulas by design won't accept whole columns as range specifiers, so exact ranges must be provided:

    {=SUM((A1:A34>=E1)*(A1:A34<=E2))} [ entered with control+shift+enter ]

    =SUMPRODUCT((A1:A34>=E1)*(A1:A34<=E2)) [ normally entered ]

    Aladin
    Microsoft MVP - Excel

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing values in a column (2000/SR-1)

    <font color=red>Please ignore my post below. It was too late when I replied and I misread what Aladin's formula was doing.</font color=red>


    I believe his formula should have been:

    <pre>=COUNTIF(A:A,">="&E1)-COUNTIF(A:A,"<="&E2)
    </pre>


    In addition, E1 should contain 1 and E2 should contain 75 not .75 and 1 as he stated.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •