CountA and ArrayFormula

[ron]

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?

 

[Peo Sjoblom]

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]

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]

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]

You have been most helpful. Those links were very helpful.

Thank you

Ron La Force