You are presumably trying to add values from the range CrseEvalData!H6:H50
where that range includes text rather than numbers? SUMPRODUCT wants to see
numbers.
--
David Biddulph

UT wrote:
Sorry, I tried both options but none worked.

=SUMPRODUCT((CrseEvalData!H6:H50<"Cable,T1,DSL")* (CrseEvalData!H6:H50<"")*CrseEvalData!H6:H50)
gave me "value" error.

The second option:

=SUMPRODUCT(--(CrseEvalData!H6:H50<"Cable"),--(CrseEvalData!H6:H50<"T1"),--(CrseEvalData!H6:H50<""),CrseEvalData!H6:H50)
still shows 0.

What am I going wrong?
Thanks



"Shane Devenshire" wrote:

Hi,

I didn't test this, but try

=SUMPRODUCT((CrseEvalData!H6:H50<"Cable,DSL,T1")* (CrseEvalData!H6:H50<"")*CrseEvalData!H6:H50)

If you want to use the --(...) form you will probably need to
reference each item, Cable, DSL, TI separately:
--(CrseEvalData!H6:H50<"Cable"),--(CrseEvalData!H6:H50<"T1")
and so on.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"UT" wrote:

The formula did not work. It shows 0 although there is data. Any
other ideas?

Thanks.

"lightbulb" wrote:

You need to include what reference you want added...for example if
you're wanting to add all values in H6:H50 that don't equal
"Cable, DSL, T1" or "" then use the following formula;

=SUMPRODUCT(--(CrseEvalData!H6:H50<"Cable,DSL,T1"),--(CrseEvalData!H6:H50<""),CrseEvalData!H6:H50)

Note that if you're not wantin it is only set up to not add ones
that read "Cable, DSL, T1", it will add ones that read "Cable" or
"DSL" or "T1". If H6:H50 were not the ones you were wanting to
add, for example if it was I6:I50, then that would be the
reference you put at the end in the formula above.

Hope this helps!!
If so, click Yes!

"UT" wrote:

Hi,

I am trying to count number of values in a certain column but at
the same
time it should exclude some other values in the column from
another sheet.
For instance I want to what other internet connection the users
used
excluding Cable, DSL, T1 etc.
=SUMPRODUCT(--(CrseEvalData!H6:H50<"Cable,DSL,T1"),--(CrseEvalData!H6:H50<""))
function does not work. It counts all the values in the column.

What function can I use for my purpose?
Thanks.