Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountA and ArrayFormula
The following 'Sum' array formula works:
=SUM(IF($J$6:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$6:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$H$6:$H$500,0),0)) Unfortunately, the 'Counta' array formula version =COUNTA(IF($J$8:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$8:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$C$8:$C$500,0),0)) doesn't seem to work. What have I done wrong, or forgotten to do? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountA and ArrayFormula
It is better that you tell us what you want to do instead of us having to
audit a formula that doesn't work. First of all I don't understand why you are using datevalue, what's in Q11 and what are in J6:J500 If you want to sum what's in H6:H500 between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),$H$6:$H$500) entered normally, so need for any array entered formulas, also note that if you have dates in Q11 and R11 and J6:J500 hold dates then there is no need whatsoever for TEXT or DATEVALUE, DATEVALUE is not really needed anyway Now to your second formula, if you want to count non blank cells in C between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),--($C$6:$C$500<"")) -- Regards, Peo Sjoblom "ron" wrote in message ... The following 'Sum' array formula works: =SUM(IF($J$6:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$6:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$H$6:$H$500,0),0)) Unfortunately, the 'Counta' array formula version =COUNTA(IF($J$8:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$8:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$C$8:$C$500,0),0)) doesn't seem to work. What have I done wrong, or forgotten to do? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountA and ArrayFormula
Peo S,
I apologize for not including all pertinent information. I had no idea that 'Sumproduct' could be used for anything but what its name suggests - multiplication. Where do I go in the help file to find out how to use 'Sumproduct' in the manner in which you used it and other non-intuitive uses? Why are there 2 dashes in front of the internal formulas? Is this a technique I can use in other formulas? Where in the help file do I find an explanation of how to use 2 dashes in formulas? "Peo Sjoblom" wrote: It is better that you tell us what you want to do instead of us having to audit a formula that doesn't work. First of all I don't understand why you are using datevalue, what's in Q11 and what are in J6:J500 If you want to sum what's in H6:H500 between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),$H$6:$H$500) entered normally, so need for any array entered formulas, also note that if you have dates in Q11 and R11 and J6:J500 hold dates then there is no need whatsoever for TEXT or DATEVALUE, DATEVALUE is not really needed anyway Now to your second formula, if you want to count non blank cells in C between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),--($C$6:$C$500<"")) -- Regards, Peo Sjoblom "ron" wrote in message ... The following 'Sum' array formula works: =SUM(IF($J$6:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$6:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$H$6:$H$500,0),0)) Unfortunately, the 'Counta' array formula version =COUNTA(IF($J$8:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$8:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$C$8:$C$500,0),0)) doesn't seem to work. What have I done wrong, or forgotten to do? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountA and ArrayFormula
No worries, I was just a little bit early morning grumpy
Unfortunately Microsoft does not know either that this is possible using SUMPRODUCT so no help in help, fortunately Bob Phillips has written down some info and so has J.E McGimpsey here are 2 links http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.mcgimpsey.com/excel/formulae/doubleneg.html HTH -- Regards, Peo Sjoblom "ron" wrote in message ... Peo S, I apologize for not including all pertinent information. I had no idea that 'Sumproduct' could be used for anything but what its name suggests - multiplication. Where do I go in the help file to find out how to use 'Sumproduct' in the manner in which you used it and other non-intuitive uses? Why are there 2 dashes in front of the internal formulas? Is this a technique I can use in other formulas? Where in the help file do I find an explanation of how to use 2 dashes in formulas? "Peo Sjoblom" wrote: It is better that you tell us what you want to do instead of us having to audit a formula that doesn't work. First of all I don't understand why you are using datevalue, what's in Q11 and what are in J6:J500 If you want to sum what's in H6:H500 between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),$H$6:$H$500) entered normally, so need for any array entered formulas, also note that if you have dates in Q11 and R11 and J6:J500 hold dates then there is no need whatsoever for TEXT or DATEVALUE, DATEVALUE is not really needed anyway Now to your second formula, if you want to count non blank cells in C between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),--($C$6:$C$500<"")) -- Regards, Peo Sjoblom "ron" wrote in message ... The following 'Sum' array formula works: =SUM(IF($J$6:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$6:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$H$6:$H$500,0),0)) Unfortunately, the 'Counta' array formula version =COUNTA(IF($J$8:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$8:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$C$8:$C$500,0),0)) doesn't seem to work. What have I done wrong, or forgotten to do? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountA and ArrayFormula
You have been most helpful. Those links were very helpful.
Thank you Ron La Force "Peo Sjoblom" wrote: No worries, I was just a little bit early morning grumpy Unfortunately Microsoft does not know either that this is possible using SUMPRODUCT so no help in help, fortunately Bob Phillips has written down some info and so has J.E McGimpsey here are 2 links http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.mcgimpsey.com/excel/formulae/doubleneg.html HTH -- Regards, Peo Sjoblom "ron" wrote in message ... Peo S, I apologize for not including all pertinent information. I had no idea that 'Sumproduct' could be used for anything but what its name suggests - multiplication. Where do I go in the help file to find out how to use 'Sumproduct' in the manner in which you used it and other non-intuitive uses? Why are there 2 dashes in front of the internal formulas? Is this a technique I can use in other formulas? Where in the help file do I find an explanation of how to use 2 dashes in formulas? "Peo Sjoblom" wrote: It is better that you tell us what you want to do instead of us having to audit a formula that doesn't work. First of all I don't understand why you are using datevalue, what's in Q11 and what are in J6:J500 If you want to sum what's in H6:H500 between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),$H$6:$H$500) entered normally, so need for any array entered formulas, also note that if you have dates in Q11 and R11 and J6:J500 hold dates then there is no need whatsoever for TEXT or DATEVALUE, DATEVALUE is not really needed anyway Now to your second formula, if you want to count non blank cells in C between 2 dates you can use =SUMPRODUCT(--($J$6:$J$500=Q11),--($J$6:$J$500<=R11),--($C$6:$C$500<"")) -- Regards, Peo Sjoblom "ron" wrote in message ... The following 'Sum' array formula works: =SUM(IF($J$6:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$6:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$H$6:$H$500,0),0)) Unfortunately, the 'Counta' array formula version =COUNTA(IF($J$8:$J$500=DATEVALUE(TEXT(Q11,"mm/dd/yy")),IF($J$8:$J$500<=DATEVALUE(TEXT(R11,"mm/dd/yy")),$C$8:$C$500,0),0)) doesn't seem to work. What have I done wrong, or forgotten to do? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTA() | Excel Discussion (Misc queries) | |||
COUNTA AND | Excel Discussion (Misc queries) | |||
using COUNTA | Excel Discussion (Misc queries) | |||
COUNTA IF(NOT) | Excel Discussion (Misc queries) | |||
COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions |