Countif in two separate columns?

[robcunliffe]

How do i count the number of cells in a column containing "offer withdrawn". That have actually been withdrawn between two dates. The date is shoun in another column.

The AND function doesn't appear to work, when used within the COUNTIF function. im stuck now, can anyone help??

This formula counts "offer withdrawn" in column g.

=COUNTIF('Clearence Status sheet'!G:G,"Offer Withdrawn")

ive tried this: but it didnt work

=COUNTIF('Clearence Status sheet'!G:G,AND("Offer Withdrawn",COUNTIF('Clearence Status sheet'!L:L,">=38108")-COUNTIF('Clearence Status sheet'!L:L,">38138")))

NOTE: the 38108 and 38138 should count all dates in May 2004.

Can anyone help??

Many thanks

Rob

[duane]

try this

=sumproduct(('Clearence Status sheet'!G1:G1000="Offer Withdrawn")*('Clearence Status sheet'!L1:L1000>=38108)*('Clearence Status sheet'!L1:L1000<38138))

note that a row range needs to be specified - i used rows 1:1000

[robcunliffe]

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

[duane]

I suspect this will do it - average values in column u<= 1000 for your date range

=sumproduct(('Clearence Status sheet'!u1:u1000<=1000)*('Clearence Status sheet'!L1:L1000>=38108)*('Clearence Status sheet'!L1:L1000<38138)*('Clearence Status sheet'!u1:u1000))/sumproduct(('Clearence Status sheet'!u1:u1000<=1000)*('Clearence Status sheet'!L1:L1000>=38108)*('Clearence Status sheet'!L1:L1000<38138))

[robcunliffe]

many thanks

but i get #value!

any ideas?

Rob

[Domenic]

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!