Countif within a range

[MollyElizabeth]

I am trying to figure out how to do a countif within a range with 1 addition criteria. I have miles from our facility to our customers. I have already used the following formula to get the total number of trips to the customers within a mileage range for the first 2 months of the year, =SUM(INDEX((Sheet1!$Q$2:$Q$3733>=A3)*(Sheet1!$Q$2:$Q$3733<=B3),0,1)). Now I need to figure out by day of week how many times we are going to to customers within that mileage range. I have already add a column into the raw data that returns the DOW value (1,2,3,4,5,7). I just can't figure out how to add the extra criteria to the countif. An example of what I want to see if for 0-20 miles on a Tueday how many trips did we run.

 

[ChrisM]

As long as you have Excel 2007+, just use the =COUNTIFS() formula.

 

[MollyElizabeth]

The COUNTIFS works ok, but if the criteria links to a cell the formula won't work. For example, if I enter that <=B3 the only return i ever get is 0. I have to put in as criteria that <=50. I really want to be able to change my ranges easily without having to manually redo every cell, or do a find replace on every cell. Here is an example of what I am trying to create.

		Deliveries per Week
Weekly Sales	1	2	3	4	5	6	7
$0	$10,000	20	10	0	1	0	0	0
$10,000	$20,000	3	19	4	0	0	0	0
$20,000	$30,000	0	32	12	2	1	0	0
$30,000	$40,000	1	24	17	4	0	0	0
$40,000	$50,000	1	18	21	7	0	0	0
$50,000	$60,000	0	2	11	12	0	0	0
$60,000	$70,000	0	3	7	14	1	0	0
$70,000	$80,000	1	0	14	11	2	0	0
$80,000	$90,000	0	0	10	9	1	0	0
$90,000	$100,000	0	0	12	10	1	0	0
$100,000	$110,000	1	0	3	6	1	1	0
$110,000	$120,000	0	0	2	5	2	0	0
$120,000	$130,000	0	0	5	4	0	0	0
$130,000	$140,000	0	0	0	6	1	0	0
>$140,000		0	0	3	5	4	1	1

<colgroup><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[StefanVL]

The COUNTIFS works ok, but if the criteria links to a cell the formula won't work. For example, if I enter that <=B3 the only return i ever get is 0. I have to put in as criteria that <=50. I really want to be able to change my ranges easily without having to manually redo every cell, or do a find replace on every cell. Here is an example of what I am trying to create.

		Deliveries per Week
Weekly Sales	1	2	3	4	5	6	7
$0	$10,000	20	10		1
$10,000	$20,000	3	19	4
$20,000	$30,000		32	12	2	1
$30,000	$40,000	1	24	17	4
$40,000	$50,000	1	18	21	7
$50,000	$60,000		2	11	12
$60,000	$70,000		3	7	14	1
$70,000	$80,000	1		14	11	2
$80,000	$90,000			10	9	1
$90,000	$100,000			12	10	1
$100,000	$110,000	1		3	6	1	1
$110,000	$120,000			2	5	2
$120,000	$130,000			5	4
$130,000	$140,000				6	1
>$140,000				3	5	4	1	1

<tbody>
</tbody>

For the criteria in the countifs try: "<="&B3

 

[ChrisM]

Yep, like this for example:

=COUNTIFS(A5:A19,">=" & J8,C5:C19,">1")

 

[MollyElizabeth]

Thank you all for your help! Works great!!