Countif Multiple Criteria
Author Message
tlawler13

 Posted: Thu Jun 14 10:32:01 CDT 2007 Top
 worksheet functions >> Countif Multiple Criteria 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. Excel61

Teethlessmama

 Posted: Thu Jun 14 10:32:01 CDT 2007 Top
 worksheet functions >> Countif Multiple Criteria =SUMPRODUCT(--(D9:D55>DATEVALUE("12/31/2006")),--(D9:D55<=K1),E9:E55)/(COUNTIF(D9:D55,">12/31/2006")-COUNTIF(D9:D55,K1)) > 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.

David

 Posted: Thu Jun 14 10:36:17 CDT 2007 Top
 worksheet functions >> Countif Multiple Criteria 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)) ? -- David Biddulph > 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.

PCLIVE

 Posted: Thu Jun 14 10:47:26 CDT 2007 Top
 worksheet functions >> Countif Multiple Criteria 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- > > 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.