Countif Function with Multiple Conditions

[Montano]

If you have a column of percentages, and you need to get the sum of how many cells fall into designated ranges, how do you use the countif function with multiple criteria?
For example, how do you get the number of cells that contain the percentages that are greater than 60% but less than 70%?

 

[SkipVought]

Hi,

Use SUMPRODUCT like
[tt]
=SUMPRODUCT((PERCT>.6)*(PERCT<.7)*(SUMCOL))
[/tt]
where I used Insert/Name/Create - Create names in TOP row and my headings are PERCT & SUMCOL


Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[Montano]

I may have posed the question wrong. I basically want to know in a column of percentages, I want to break out the percentages into groupings. For example:

<50%
51% to 60%
61% to 70%
71% to 80%
>81%

If there is a column of 50 percentages, how many fall into each bucket?

 

[SkipVought]

can you see that
[tt]
=SUMPRODUCT((PERCT>.6)*(PERCT<.7)*(SUMCOL))
[/tt]
has a from and to value???

for instance, if you had your from and to in separate columns labeled as such then
[tt]
=SUMPRODUCT((PERCT>=From)*(PERCT<To)*(SUMCOL))
[/tt]


Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[xlbo]

Personally, I would use a lookup table + VLOOKUP with the 4th argument set to TRUE. Have a look in the help files for more details.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

This is exactly what the FREQUENCY function was created for ... it will count the occurences for each bucket.

Set up 4 cells ( vertically ) with the values 50%, 60%, 70%, and 80%, and then select the adjacent cells ( plus one extra cell ). Now type the formula:
=FREQUENCY(Cells_with_data,the_4_bins_cells)
and press Ctrl-Shift-Enter instead of Enter.

( replace the Cells_with_data part with the reference to your list of percentages that you want to analyse, and replace the the_4_bins_cells with the reference to the 4 cells containing the bins. )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.