| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Multiple Criteria
Hi-
The following array formula is working for me: =(SUMPRODUCT(--(D9:D55DATEVALUE("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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Multiple Criteria
=SUMPRODUCT(--(D9:D55DATEVALUE("12/31/2006")),--(D9:D55<=K1),E9:E55)/(COUNTIF(D9:D55,"12/31/2006")-COUNTIF(D9:D55,K1))
"jackie" wrote: Hi- The following array formula is working for me: =(SUMPRODUCT(--(D9:D55DATEVALUE("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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif Multiple Criteria
You didn't say what formula you tried. Was it
=(SUMPRODUCT(--(D9:D55DATEVALUE("12/31/2006")),(--(D9:D55<=K1)),E9:E55))/(COUNTIF(D9:D55,"12/31/2006")-COUNTIF(D9:D55,""&K1)) ? -- David Biddulph "jackie" wrote in message ... Hi- The following array formula is working for me: =(SUMPRODUCT(--(D9:D55DATEVALUE("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. |
|
#4
Posted to microsoft.public.excel.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:D55DATEVALUE("12/31/2006")),(--(D9:D55<=VALUE(K1))),E9:E55))/(COUNTIF(D9:D55,"12/31/2006")-COUNTIF(D9:D55,"" & VALUE(K1))) HTH, Paul "jackie" wrote in message ... Hi- The following array formula is working for me: =(SUMPRODUCT(--(D9:D55DATEVALUE("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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif Multiple Criteria | Excel Discussion (Misc queries) | |||
| Countif, Multiple criteria | Excel Discussion (Misc queries) | |||
| Countif Multiple Criteria | Excel Discussion (Misc queries) | |||
| Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
