That formula doesn't need to be array-entered, and it doesn't need the
dollar signs which you seem to have added.

If it gives a #VALUE! error, it is because at least one cell in your range
'2010'!$BE$6:$BE$6000 contains a #VALUE! error. Cure that first, and then
your formula has a chance to work.
--
David Biddulph


"Dave" wrote in message
...
I have tried all of the suggestions and still get the error. I am entering
the formula on a TAB named Detailed Activity and referring to cells on a
TAB
named 2010. Following is a copy of the formula I entered for formula 3:
{=SUMPRODUCT(('2010'!$BE$6:$BE$6000=1)*(ISNUMBER(' 2010'!G$6:G$6000)))}
I added the { in this post-Excel added them in the formula as you
suggested.
Any ideas what I am doing wrong?
Thanks,
"Tom Hutchins" wrote:

I was only able to get a #VALUE error with the second formula, and only
if
column D had cells in the formula's range that contained spaces or text.
Try
this version of the second formula:

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*IF(ISTEXT(D1:D100),0,D1:D100))

Please note that this is an array formula. After entering the formula
press
CTRL+SHIFT+ENTER instead of just ENTER. If successful, in the Formula Bar
you
can notice curly braces at both ends like "{=<formula}". Excel adds the
curly braces; you can't add them yourself.

Hope this helps,

Hutch

"Dave" wrote:

I tried them but I get the #VALUE! error.

"Jacob Skaria" wrote:

'Try the below

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*
(ISNUMBER(D1:D100)))

=SUMPRODUCT((A1:A100=1)*(B1:B100="Yes")*(C1:C100=" Prime")*D1:D100)

=SUMPRODUCT((A1:A100=1)*(ISNUMBER(D1:D100)))

If this post helps click Yes
---------------
Jacob Skaria


"Dave" wrote:

I have a spreadsheet I use for sales management and need help with
3 formulas
I cannot figure out. Following is what I am trying to do:
1. If the value in cells A1:A100=1 and the value in cells
B1:B100=Yes and
the value in cells C1:C100=Prime, count the cells in D1:D100 that
have a
value in them. (column D contains numbers.)
2. Same as number 1 but instead of counting, sum the cells in
D1:D100 that
have a value in them.
3. If the value in cells A1:A100=1, then count the cells in
D1:D100 that
have a value in them. (column D contains numbers.)
Any help would be greatly appreciatied!