Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula error- ("Nesting more than 7 functions"- Feb4)
I have tried the following formula as suggested by your discussion group Feb4,
=sum(WS2!$A$1:index(WS2!$A1:$L1,countif($A1:$L1," "&0))). The formula should add the values of cells WS2(A1:L1) only if there is a value 0 in (A1:L1). ie: if WS2 A1=200 and WS2 B1=150 and WS2 (C1:L1) are all 0 then the formula should render the value 350. This formula as written above is adding all of the cells (WS2A1:L1) even if the values of (A1:L1) are all 0. Note that when I have referenced (A1:L1) it refers to WS1 not WS2. I appreciate your help with this formula so that it only adds when any of the cells in (A1:L1) are 0. Thanks very much. -- Brian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula error- ("Nesting more than 7 functions"- Feb4)
Here is some information which will help you get better solutions:
1. This is not "your discussion group". This is no one's discussion group. It is a group of users who volunteer their time to help others. There is no one in charge, and people come and go as they please. 2. What does the description of your problem have to do with the subject line? How do you expect to attract people who can solve your problem with the subject line you used? 3. What is the problem you are having? What solution are you looking for? Regards, Fred. "Brian" wrote in message ... I have tried the following formula as suggested by your discussion group Feb4, =sum(WS2!$A$1:index(WS2!$A1:$L1,countif($A1:$L1," "&0))). The formula should add the values of cells WS2(A1:L1) only if there is a value 0 in (A1:L1). ie: if WS2 A1=200 and WS2 B1=150 and WS2 (C1:L1) are all 0 then the formula should render the value 350. This formula as written above is adding all of the cells (WS2A1:L1) even if the values of (A1:L1) are all 0. Note that when I have referenced (A1:L1) it refers to WS1 not WS2. I appreciate your help with this formula so that it only adds when any of the cells in (A1:L1) are 0. Thanks very much. -- Brian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula error- ("Nesting more than 7 functions"- Feb4)
I think that the SUMPRODUCT function might work better for you he
=SUMPRODUCT(--($A1:$L10),'WS2'!$A$1:$L$1) HTH Elkar "Brian" wrote: I have tried the following formula as suggested by your discussion group Feb4, =sum(WS2!$A$1:index(WS2!$A1:$L1,countif($A1:$L1," "&0))). The formula should add the values of cells WS2(A1:L1) only if there is a value 0 in (A1:L1). ie: if WS2 A1=200 and WS2 B1=150 and WS2 (C1:L1) are all 0 then the formula should render the value 350. This formula as written above is adding all of the cells (WS2A1:L1) even if the values of (A1:L1) are all 0. Note that when I have referenced (A1:L1) it refers to WS1 not WS2. I appreciate your help with this formula so that it only adds when any of the cells in (A1:L1) are 0. Thanks very much. -- Brian |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula error- ("Nesting more than 7 functions"- Feb4)
Hi,
you can use a simple sumif function =sumif(WS2!$A1:$L1,"0",) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Brian" wrote in message ... I have tried the following formula as suggested by your discussion group Feb4, =sum(WS2!$A$1:index(WS2!$A1:$L1,countif($A1:$L1," "&0))). The formula should add the values of cells WS2(A1:L1) only if there is a value 0 in (A1:L1). ie: if WS2 A1=200 and WS2 B1=150 and WS2 (C1:L1) are all 0 then the formula should render the value 350. This formula as written above is adding all of the cells (WS2A1:L1) even if the values of (A1:L1) are all 0. Note that when I have referenced (A1:L1) it refers to WS1 not WS2. I appreciate your help with this formula so that it only adds when any of the cells in (A1:L1) are 0. Thanks very much. -- Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
can I nest COUNTIF functions(C5:C8,"apples") AND (D5:D8,"green") | Excel Worksheet Functions | |||
Functions for "current" & "previous" month to calculate data | Excel Worksheet Functions | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) |