Countif

[johns99]

Trying to setup a formula that will count the amount of bonuses that fall within a specific range...for example

Count if the bonus ranges from $0-$4999...suggestions?

thanks in advance.

Range</SPAN>
$0.00 </SPAN>	$4,999.00 </SPAN>
$5,000.00 </SPAN>	$9,999.00 </SPAN>
$10,000.00 </SPAN>	$14,999.00 </SPAN>
$15,000.00 </SPAN>	$19,999.00 </SPAN>
$20,000.00 </SPAN>	$24,999.00 </SPAN>
$25,000.00 </SPAN>	$29,999.00 </SPAN>
$30,000.00 </SPAN>	$34,999.00 </SPAN>
$35,000.00 </SPAN>	$39,999.00 </SPAN>
$40,000.00 </SPAN>	$44,999.00 </SPAN>
$45,000.00 </SPAN>	$49,999.00 </SPAN>
$50,000.00 </SPAN>	$54,999.00 </SPAN>
$55,000.00 </SPAN>	$59,999.00 </SPAN>
$60,000.00 </SPAN>	$64,999.00 </SPAN>
$65,000.00 </SPAN>	$69,999.00 </SPAN>
$70,000.00 </SPAN>	$74,999.00 </SPAN>
$75,000.00 </SPAN>	$79,999.00 </SPAN>
$80,000.00 </SPAN>	$84,999.00 </SPAN>
$85,000.00 </SPAN>	$89,999.00 </SPAN>
$90,000.00 </SPAN>	$94,999.00 </SPAN>
$95,000.00 </SPAN>	$99,999.00 </SPAN>
$100,000.00 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

 

[johns99]

One more thing...the ranges are in columns G and H...so $0 is in G and $4999 is in H.

is there a way to do a COUNTIF >= G3 <=H3?

thanks,

 

[AliGW]

=COUNTIF(range_to_be_counted,"<=4999")

 

[bbott]

Assuming your bonuses are in column C:

=COUNTIF(C:C," < 4999")

 

[johns99]

I was looking for something that looked at the range between 0 - 4999, and that references the cells that their in...for instance $0 is in G3 and 4999 is in H3.

How would I be able to setup a formula that takes a count if >= G3 and <= H3? This way I can drag down and not have to manually type "4999"

 

[johns99]

I tried doing this and it didn't work....

=COUNTIF($E$2:$E$179,AND($E$2:$E$179>=$G3,$E$2:$E$179<=$H3))

 

[bbott]

What are you counting, the number of bonuses in column E that fit in a range?

 

[johns99]

Yes

 

[johns99]

The ranges are above...

 

[ArthriticPanda]

Hi Johns99,

I think I've found solution using helper columns (which can be hidden)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	0	4999		From:	To:				Total column B	Difference	From:	To:
2	5000	9999		36000	50000	=	3		20	7	13	10
3	10000	14999
4	15000	19999
5	20000	24999
6	25000	29999
7	30000	34999
8	35000	39999
9	40000	44999
10	45000	49999
11	50000	54999
12	55000	59999
13	60000	64999
14	65000	69999
15	70000	74999
16	75000	79999
17	80000	84999
18	85000	89999
19	90000	94999
20	95000	99999
21	100000
Sheet1

Hopefully you can follow whats going on.

Regards,

AP