Average individual cell values between two columns and count the number over X

[WaqasTariq]

I have two columns in Excel:

Column A	Column B
Res Qty	Total Hrs
2	66
1	30	Res over 32:	10
3	93
3	102
5	180
5	155
... row #57	... row #57

<tbody>
</tbody>

(66/2 = 33, 30/1 = 30, 93/3 = 31, 102/3 = 34, 180/5 = 36, 155/5 = 31) > 32 count = 10

Question: I want to count how many resources work over 32 hours in cell C3. Is there a formula in Excel that will help me do that?

 

[Fluff]

How about

Book1
	A	B	C	D	E
2	2	66	33		3
3	1	30	30
4	3	93	31
5	3	102	34
6	5	180	36
7	5	155	31
Log

Cell Formulas
Range	Formula
E2	{=SUM(IF((B2:B7/A2:A7)>32,1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

But all cells need to have values

 

[WaqasTariq]

How about

But all cells need to have values

@Fluff, Thank you, but the answer (3) is incorrect. The correct answer would be 10. (66/2 = 33, 30/1 = 30, 93/3 = 31, 102/3 = 34, 180/5 = 36, 155/5 = 31) > 32 count = 10

 

[Fluff]

How do you work that out?

 

[Fluff]

I think I've got it, try
=SUM(IF((B2:B7/A2:A7)>32,A2:A7,0))
Also array entered.

 

[WaqasTariq]

I think I've got it, try
=SUM(IF((B2:B7/A2:A7)>32,A2:A7,0))
Also array entered.

That worked out perfectly, thank you!

 

[Fluff]

You're welcome & thanks for the feedback

 

[WaqasTariq]

@Fluff Thank you for all your help yesterday. If you have time, could you please let me know how I would add the following to the above?


Count resources over 32 but less than 36?

edit: Nevermind, figured it out.

Answer: =(SUM(IF((B2:B7/A2:A7)>32,A2:A7,0)))-(SUM(IF((B2:B7/A2:A7)<36,A2:A7,0)))

 

[Fluff]

Your formula gives me -4 rather than 5
try
=SUM(IF(((B2:B7/A2:A7)>32)*((B2:B7/A2:A7)<36),A2:A7,0))
or
=SUMPRODUCT(((B2:B7/A2:A7)>32)*((B2:B7/A2:A7)<36),A2:A7)