![]() |
SUMIF "value if false" not working
Hi Experts,
I just offered this as a solution to a post in public.excel. Now I notice that while the "value if true" part works the "value if false" does not, per below, using Enter and/or Array-Enter. In a Help search it said the "value if false" was optional, but if you include it should it not work? Where A1=X and B1= 1 and C1= 2. If A1=Z then I get #VALUE instead of xx. Enter =SUM(IF(A1="X",B1+C1,"xx")) A1=X Correct C+S+E =SUM(IF(A1="X",B1+C1,"xx")) A1=X Correct =SUM(IF(A1="X",B1+C1,"xx")) A1=Z #VALUE Enter =SUM(IF(A1="X",B1+C1,"xx")) A1=Z #VALUE Thanks. Regards, Howard |
SUMIF "value if false" not working
wrote:
Now I notice that while the "value if true" part works the "value if false" does not, per below, using Enter and/or Array-Enter. Why would you array-enter the formula below? It does not have any array or range parameters. (But the formula that you did offer does need to be array-entered -- although it is nonsensical exactly you wrote it.) wrote: Enter [....] =SUM(IF(A1="X",B1+C1,"xx")) A1=Z #VALUE Read the SUM help page carefully. SUM ignores text in a parameter only if "an argument is an array or reference". In contrast, "if any arguments are text that cannot be translated into numbers, Excel displays an error". For example, SUM("xx") return an Excel error. The SUM expression could be written as follows (and normally-entered): =SUM(IF(A1="X",B1+C1,0)) Arguably, SUM is not needed at all in this context. It is sufficient to write: =IF(A1="X",B1+C1,0) However, I believe your example is intended to be a distillation of an array-entered formula of the form: =SUM(IF(A1:A100="X",B1:B100+C1:C100,0)) In that context, we do not need the value-if-false part; it returns FALSE by default. Moreover, in that context, we could replace 0 with "xx" since IF(A1:A100="X",...) returns an array. But really, there is no reason to do that. |
SUMIF "value if false" not working
On Sunday, February 3, 2013 11:41:15 PM UTC-8, joeu2004 wrote:
wrote: Now I notice that while the "value if true" part works the "value if false" does not, per below, using Enter and/or Array-Enter. Why would you array-enter the formula below? It does not have any array or range parameters. (But the formula that you did offer does need to be array-entered -- although it is nonsensical exactly you wrote it.) wrote: Enter [....] =SUM(IF(A1="X",B1+C1,"xx")) A1=Z #VALUE Read the SUM help page carefully. SUM ignores text in a parameter only if "an argument is an array or reference". In contrast, "if any arguments are text that cannot be translated into numbers, Excel displays an error". For example, SUM("xx") return an Excel error. The SUM expression could be written as follows (and normally-entered): =SUM(IF(A1="X",B1+C1,0)) Arguably, SUM is not needed at all in this context. It is sufficient to write: =IF(A1="X",B1+C1,0) However, I believe your example is intended to be a distillation of an array-entered formula of the form: =SUM(IF(A1:A100="X",B1:B100+C1:C100,0)) In that context, we do not need the value-if-false part; it returns FALSE by default. Moreover, in that context, we could replace 0 with "xx" since IF(A1:A100="X",...) returns an array. But really, there is no reason to do that. Thanks, joeu. If I can re-find that page I'll read it closer. This little formula purely slipped between my mental cracks. =IF(A1="X",B1+C1,0) Regards, Howard |
All times are GMT +1. The time now is 08:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com