would this formula help?
presuming your numbers to sum up are in I6:I50

=SUMPRODUCT((CrseEvalData!H6:H50<"Cable")*(CrseEv alData!H6:H50<"DSL")
*(CrseEvalData!H6:H50<"T1"),--(CrseEvalData!I6:I50<Â*""))


however if you would like to count the number of occurences of
internet connections other then "Cable" and "DSL" and "T1" I would
calculate it as follows:
=50-6+1=number of cells in H6:H50=45

and use the following formula:
=45-COUNTIF(H6:H50;"Cable")-COUNTIF(H6:H50;"DSL")-COUNTIF(H6:H50;"T1")




On 2 Cze, 19:33, 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.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -