Count total values excluding some data
 

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.

Count total values excluding some data
 

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.

Count total values excluding some data
 

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.

Count total values excluding some data
 

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.

Count total values excluding some data
 

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 -

Count total values excluding some data
 

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.

Count total values excluding some data
 

Your formula below worked-

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

Thanks a lot!!


"Jarek Kujawa" wrote:

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 -

Count total values excluding some data
 

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.