Countif in two separate columns?

Many thanks that did the trick, but im stuck again on this one.

Ive worked out an average using this command.

=SUMIF('Clearence Status sheet'!U:U,"<=1000")/COUNTIF('Clearence Status sheet'!U:U,"<=1000")

but i now need to add the date constraints as per my previous post.

Can you help again?

Many thanks

Rob

many thanks

but i get #value!

any ideas?

Rob

Maybe one of the cells in your ranges contains a #VALUE! error. If so, modify the formula to avoid the error value.

Having said that, here's another formula to give you your average...

=AVERAGE(IF(('Clearence Status sheet'!L1:L1000>=38108)*('Clearence Status sheet'!L1:L1000<38138)*('Clearence Status sheet'!U1:U1000<=1000),'Clearence Status sheet'!U1:U1000))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

that works a treat, i just have to understand it now!

p.s. why do i have to use ctrl+shift+enter? why doesnt it work without using it.

thanks

CONTROL+SHIFT+ENTER is used whenever you create an array formula. See the help menu under 'Array formulas and how to enter them' for a detailed explanation.

Hope this helps!