Count number of occurances below a range, within a range, and below a range

**maacmaac** · 09-15-2013, 11:07 PM

I have a table, which contains sales number for a number of zones. For each zone, there is a range of acceptable sales. For each zone, I want to identify (1) the number of sales which were below the range (2) the number of sales which were within the range (3) the number of sale which were above the range.

For example if the sales range for Zone1 is $250 to $800, I want to count the number of sales which were below $250, the number of sales which were between $250 and $800 and the number of sales which were above $800.

I did try to use a SumProduct formula but couldn't figure out how to account for the 3 different ranges so not sure if that will even work. Note: the range of values for each zone does change month to month. I have attached a sample workbook with sample table of data and expected results. Thanks in advance for any comments.

**FDibbins** · 09-15-2013, 11:14 PM

Try this, copied across...
below =COUNTIFS($A$2:$A$20,$A26,$C$2:$C$20,"<250")
within =COUNTIFS($A$2:$A$20,$A26,$C$2:$C$20,">=250",$C$2:$C$20,"<800")
above =COUNTIFS($A$2:$A$20,$A26,$C$2:$C$20,">800")

If you put the ranges next to the zone in the 2nd table, you can reference the values instead of hard-coding them

the sumproduct version wold be similar...
below =SUMPRODUCT(($A$2:$A$20=A26)*($C$2:$C$20<250))
within =SUMPRODUCT(($A$2:$A$20=A26)*($C$2:$C$20>=250)*($C$2:$C$20<800))

**ramananhrm** · 09-15-2013, 11:27 PM

Please try this file.

**humdingaling** · 09-15-2013, 11:36 PM

Here's my attempt

Ramananhrm one is more streamlined compared to mine
i would use his

Count number of occurances below a range, within a range, and below a range

LinkBack

Thread Tools

Rate This Thread

Display

Count number of occurances below a range, within a range, and below a range

Re: Count number of occurances below a range, within a range, and below a range

Re: Count number of occurances below a range, within a range, and below a range

Re: Count number of occurances below a range, within a range, and below a range

Thread Information

Users Browsing this Thread

Similar Threads

count occurances of range of numbers in unknown number of cells.

Count the number of occurances of a value in a range.

[SOLVED] count number of occurances of a word in a range

Count occurances in range of cells

[SOLVED] count date occurances in range of dates...

Bookmarks

Bookmarks

Posting Permissions