Count total values excluding some data

[UT]

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.

 

[lightbulb]

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]

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

Thanks.

 

[Shane Devenshire]

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.

 

[Jarek Kujawa]

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

=SUMPRODUCT((CrseEvalData!H6:H50<>"Cable")*(CrseEvalData!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")

 

[UT]

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

 

[UT]

Your formula below worked-

=SUMPRODUCT((CrseEvalData!H6:H50<>"Cable")*(CrseEvalData!H6:H50<>"DSL")
*(CrseEvalData!H6:H50<>"T1"),--(CrseEvalData!H6:H50<­>"")) even though the
values are in the same column.

Thanks a lot!!

 

[David Biddulph]

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.