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

[WaqasTariq]

I have two columns in Excel:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Column A [/TD]
[TD="align: center"]Column B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Res Qty[/TD]
[TD="align: center"]Total Hrs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]30[/TD]
[TD="align: right"]Res over 32:[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]93[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]102[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]155[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]... row #57[/TD]
[TD="align: center"]... row #57[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(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)