Countif Multiple Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jackie - 14 Jun 2007 15:46 GMT

Hi-

The following array formula is working for me:
=(SUMPRODUCT(--(D9:D55>DATEVALUE("12/31/2006")),(--(D9:D55<=K1)),E9:E55))/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,">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.

Reply to this Message

Teethless mama - 14 Jun 2007 16:32 GMT

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

> Hi-
>
[quoted text clipped - 6 lines]
>
> Thanks again.

Reply to this Message

David Biddulph - 14 Jun 2007 16:36 GMT

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

Signature

David Biddulph

> Hi-
>
[quoted text clipped - 7 lines]
>
> Thanks again.

Reply to this Message

PCLIVE - 14 Jun 2007 16:47 GMT

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(--(D9:D55>DATEVALUE("12/31/2006")),(--(D9:D55<=VALUE(K1))),E9:E55))/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,">"
& VALUE(K1)))

HTH,
Paul

> Hi-
>
[quoted text clipped - 7 lines]
>
> Thanks again.

Reply to this Message