SumProduct Formula

[pdauction]

I have the following formula which isn't currently working...

=SUMPRODUCT(COUNTA('Sampled Cases'!$BI$11:$BN$20008))*('Sampled Cases'!$CY$11:$CY$20008<>"OPEN")*('Sampled Cases'!$AO$11:$AO$20008>=sixmthdate1)*('Sampled Cases'!$AO$11:$AO$20008<=sixmthdate2)

I did try it as an array formula but this doesn't solve the problem of it always returning 0.

When I remove *('Sampled Cases'!$CY$11:$CY$20008<>"OPEN") it does work OK but obviously I do need this section in. I recognise the problem must be something to do with using the COUNTA accross multiple columns as well as rows...

I know I could use DSUM or even add a column on my 'Sampled Cases' worksheet that calculates this on each row but just wondered if anyone knew of a tweak I could make to my formula to make it work?

Forever grateful,
Paul

[martindwilson]

whats the counta bit supposed to be doing?
i suspect you just want ('Sampled Cases'!$BI$11:$BN$20008)<>"")

[pdauction]

Superb, worked a treat, not sure how I ended up over-complicating it like that.

Many thanks Martin, much appreciated.