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

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

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

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