|
|
| 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.
|
| |
|
| |
|
| |
|