=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$20)-ROW($D$1),,1)),--($D$2:$D$20="Assigned"))

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
Hi
Using Excel 2007
Would like to count cells that meet certain criteria
Would like it to work in a filtered spreadsheet to countif
only on visible cells
Need something like SUBTOTALIF
I can not work out how to do this without filtering down several times
using Subtotal function but then I lose the bigger picture
Any help appreciated