| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with a condition disregarding errors
Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
=COUNTIF($A$1:$A$50,"<"&A1)
"vsoler" skrev: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
Try this:
=SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)* ($A$1:$A$50<""),1))) ctrl+shift+enter, not just enter "vsoler" wrote: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
No need for the second IF:
=SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<"" ))) Biff "Teethless mama" wrote in message ... Try this: =SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)* ($A$1:$A$50<""),1))) ctrl+shift+enter, not just enter "vsoler" wrote: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
On 19 mayo, 20:05, "T. Valko" wrote:
No need for the second IF: =SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<"" ))) Biff "Teethless mama" wrote in message ... Try this: =SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)* ($A$1:$A$50<""),1))) ctrl+shift+enter, not just enter "vsoler" wrote: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help?- Ocultar texto de la cita - - Mostrar texto de la cita - Dear T. Valko, Your formula does not work in my Excel 2003. (A1:A50<A1) always returns FALSE because a cell cannot be smaller than itself. Can you propose a workaround? Thank you |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
Did you remember the control-shift-enter?
-- David Biddulph "vsoler" wrote in message oups.com... Dear T. Valko, Your formula does not work in my Excel 2003. (A1:A50<A1) always returns FALSE because a cell cannot be smaller than itself. Can you propose a workaround? On 19 mayo, 20:05, "T. Valko" wrote: No need for the second IF: =SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<"" ))) Biff "Teethless mama" wrote in message ... Try this: =SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)* ($A$1:$A$50<""),1))) ctrl+shift+enter, not just enter "vsoler" wrote: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help?- Ocultar texto de la cita - - Mostrar texto de la cita - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
"vsoler" wrote in message oups.com... On 19 mayo, 20:05, "T. Valko" wrote: No need for the second IF: =SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<"" ))) Biff "Teethless mama" wrote in message ... Try this: =SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)* ($A$1:$A$50<""),1))) ctrl+shift+enter, not just enter "vsoler" wrote: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help?- Ocultar texto de la cita - - Mostrar texto de la cita - Dear T. Valko, Your formula does not work in my Excel 2003. (A1:A50<A1) always returns FALSE because a cell cannot be smaller than itself. Can you propose a workaround? Thank you See this screencap: http://img72.imageshack.us/img72/6408/screencapju3.jpg Biff |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting with a condition disregarding errors
"T. Valko" wrote in message ... "vsoler" wrote in message oups.com... On 19 mayo, 20:05, "T. Valko" wrote: No need for the second IF: =SUM(IF(ISERROR(A1:A50),"",(A1:A50<A1)*(A1:A50<"" ))) Biff "Teethless mama" wrote in message ... Try this: =SUM(IF(ISERROR($A$1:$A$50),"",IF(($A$1:$A$50<A1)* ($A$1:$A$50<""),1))) ctrl+shift+enter, not just enter "vsoler" wrote: Range A1:A50 contains texts, numbers, blank cells & error cells (#N/A and #DIV/0! basically). I want to have in B1:B50 the count of the cells that are less than the corresponding cell in A1:A50 using SUMPRODUCT and disregarding the error values. I have tried with (in B1): =IF(NOT(ISERROR($A$1:$A$50)),SUMPRODUCT(--(NOT(ISERROR($A$1:$A $50)))*($A$1:$A$50<A1))) and copying down to B50. However, I keep on getting #N/A as the result of my formula. The last portion of the formula ($A$1:$A$50<A1) return #N/A that is not overcome by the preceding ISERROR conditions. Can anybody help?- Ocultar texto de la cita - - Mostrar texto de la cita - Dear T. Valko, Your formula does not work in my Excel 2003. (A1:A50<A1) always returns FALSE because a cell cannot be smaller than itself. Can you propose a workaround? Thank you See this screencap: http://img72.imageshack.us/img72/6408/screencapju3.jpg Biff P.S. Did you try "excelent's" formula? It works unless there are no numeric values in the range. I was just pointing out that TM's formula didn't need the added IF function. The modified version I posted will still fail if A1 is an error. We may need a better explanation of what you're wanting to do. Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting the number of errors in a worksheet | Excel Worksheet Functions | |||
| counting cell by color and condition | Excel Discussion (Misc queries) | |||
| Counting Errors | Excel Worksheet Functions | |||
| Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions | |||
| Counting Values on a condition | Excel Worksheet Functions | |||
Hybrid Mode
