Countif Multiple Criteria

[Guest]

Hi-

The following array formula is working for me:
=(SUMPRODUCT(--(D955>DATEVALUE("12/31/2006")),(--(D955<=K1)),E9:E55))/(COUNTIF(D955,">12/31/2006")-COUNTIF(D955,">5/31/2007")).

However I would like for 5/31/2007 to be a cell reference of K1. I want to
be able to change just K1 each month. I can not get this to work with a cell
reference. Column D has dates and Column E is numbers.

Thanks again.

 

[Guest]

=SUMPRODUCT(--(D955>DATEVALUE("12/31/2006")),--(D955<=K1),E9:E55)/(COUNTIF(D955,">12/31/2006")-COUNTIF(D955,K1))

 

[David Biddulph]

You didn't say what formula you tried. Was it
=(SUMPRODUCT(--(D955>DATEVALUE("12/31/2006")),(--(D955<=K1)),E9:E55))/(COUNTIF(D955,">12/31/2006")-COUNTIF(D955,">"&K1))
?

 

[PCLIVE]

I accidentally responded direct on this. So this is a re-response

I'm not sure exactly what this formula is supposed to accomplish, but see if
this helps.

=(SUMPRODUCT(--(D955>DATEVALUE("12/31/2006")),(--(D955<=VALUE(K1))),E9:E55))/(COUNTIF(D955,">12/31/2006")-COUNTIF(D955,">"
& VALUE(K1)))

HTH,
Paul