| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to sum number values, excluding data returned #N/A during Vloo | Excel Worksheet Functions | |||
| excel total days formula including weekends, excluding holidays | Excel Discussion (Misc queries) | |||
| Pivots: Grand total = Count not sum of data | Excel Discussion (Misc queries) | |||
| Excluding numbers in a total | Excel Discussion (Misc queries) | |||
| How do I use countif to count values excluding blank cells | Excel Worksheet Functions | |||
Linear Mode
