Formulas: Counting the number of cells that lie between 2 di

    I have a spreadsheet where I have a range of percentages.


    I want to count the number of cells that fall between two values.


    For example;


    lower value is 28%
    upper value is 35%


    I know how to use the countif function for a single criteria but am confused about how to incorporate multiple criteria using this function.


    How would I make this function work properly.


    Also, without using conditional formatting or VBA, is there something that I can type into a cell that will turn the font a certain color. I know in custom formating [] are used with the name of the color inserted in between them.


    thanks

    The SUMPRODUCT function should work. If your values are in A2:A100 the count for your limits would be:


    =SUMPRODUCT((A2:A100>=0.28)*(A2:A100<=0.35)*1)


    EDIT: for you font question: are you changing all fonts in the column, or just those that meet a criterion? If the latter, why not use Conditional Formatting? I think this might be your only option other than VBA.

    If you wanted the total of the values or the average you can use an array formula.
    If the lower and upper limites are in B1 and B2 respectively and the data as per previous the formula could be
    =SUM((A1:A102&gt;B1)*(A1:A102<B2)*A1:A102) and then press control,alt, enter to make it an array formula. It will then be enclosed { }. To get the average divide by the sumproduct formula.

    Missed a bit.
    =SUM((A1:A102&gt;B1)*(A1:A102<B2)*A1:A102) and then press control,alt, enter to make it an array formula. It will then be enclosed { }. To get the average divide by the sumproduct formula.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login