nesting problems

K

karen

this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data i now
have a
need for nine if nests i know it can only handle 7 and am looking for a new
way to look at this problem.
I need to add <>Q35,<>Q36,<>Q37
Q30 is a client name
Q29 is a 4 for current year
Q31 tru Q37 are some product codes EX: SE, EQ, LABR,SH...

Thank you for reading this
karen
 
B

Bernard Liengme

A little hard to follow without knowing what the 'story' is.
At first I thought of using AND but this will not work with array formula.
However, the multiplication equivalent will;
=SUM(IF((this=that)*(other=whatever)*........0

Any help?
 
H

Harlan Grove

karen said:
this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data i now
have a need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
Click to expand...
....

Don't use IF calls. Try

=SUMPRODUCT(
('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34)
*('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1)
*('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29),
\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000)
 
B

BenjieLop

Check out

http://www.cpearson.com/excel/nested.htm


this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data
now
have a
need for nine if nests i know it can only handle 7 and am looking for
new
way to look at this problem.
I need to add <>Q35,<>Q36,<>Q37
Q30 is a client name
Q29 is a 4 for current year
Q31 tru Q37 are some product codes EX: SE, EQ, LABR,SH...

Thank you for reading this
kare
Click to expand...
 
B

Bernard Liengme

I thought SUMPRODUCT did not work across files. Or is the catch that it
will not work unless the external file is open?

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


Harlan Grove said:
karen said:
this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data i now
have a need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
Click to expand...
...

Don't use IF calls. Try

=SUMPRODUCT(
('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34)
*('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1)
*('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29),
\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000)
Click to expand...
 
B

Bernard Liengme

Oh,dear! Experimentation shows I was wrong on both scores. What gave me
these foolish ideas?

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


Bernard Liengme said:
I thought SUMPRODUCT did not work across files. Or is the catch that it
will not work unless the external file is open?

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


Harlan Grove said:
karen said:
this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data i now
have a need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
Click to expand...
...

Don't use IF calls. Try

=SUMPRODUCT(
('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34)
*('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1)
*('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29),
\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000)
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi Bernard
you probably thought about SUMIF :))))

--
Regards
Frank Kabel
Frankfurt, Germany


Bernard said:
Oh,dear! Experimentation shows I was wrong on both scores. What gave
me these foolish ideas?


Bernard Liengme said:
I thought SUMPRODUCT did not work across files. Or is the catch that
it will not work unless the external file is open?

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


Harlan Grove said:
this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing
data i now have a need for nine if nests i know it can only handle
7 and am looking for a new way to look at this problem.
...

Don't use IF calls. Try

=SUMPRODUCT(
('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33)
*('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34)
*('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1)
*('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29),
\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000)
Click to expand...
Click to expand...
Click to expand...
 
K

karen

I have a work sheet (current.xls) with 8 columns 1(A) is client name 2(B)
the item sold 3(C) month it was sold in 4(D) year sold 7(G) dollar amount

each client has his own file and I am adding the sales up for each month and
broken down by item sold in groups, equipment, shipping, service and the
largest chemical which in its self can have up to 300 different items that
was why I did a sum IF <> the small groups but I added more small groups,
service other than contract-SOSE
equiptment other than contract-SOEQ

when I want to get all shipping charges my formula is sum if =$Q$33
the chemical is so large my formula was first intended to add all except
shipping charge(<>$Q$33) except service (<>$Q$31)

I hope this didn't make my question less able to understand
thank you for all your in put

karen
 
F

Frank Kabel

Hi
have you tried the suggested formulas? If yes what went wrong with
them?

--
Regards
Frank Kabel
Frankfurt, Germany

I have a work sheet (current.xls) with 8 columns 1(A) is client name
2(B) the item sold 3(C) month it was sold in 4(D) year sold 7(G)
dollar amount

each client has his own file and I am adding the sales up for each
month and broken down by item sold in groups, equipment, shipping,
service and the largest chemical which in its self can have up to 300
different items that was why I did a sum IF <> the small groups but I
added more small groups, service other than contract-SOSE
equiptment other than contract-SOEQ

when I want to get all shipping charges my formula is sum if =$Q$33
the chemical is so large my formula was first intended to add all
except shipping charge(<>$Q$33) except service (<>$Q$31)

I hope this didn't make my question less able to understand
thank you for all your in put

karen
karen said:
this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data
i now have a
need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
I need to add <>Q35,<>Q36,<>Q37
Q30 is a client name
Q29 is a 4 for current year
Q31 tru Q37 are some product codes EX: SE, EQ, LABR,SH...

Thank you for reading this
karen
Click to expand...
Click to expand...
 
K

karen

just tried the sumproduct didn't work get #invalid
I am adding all the amounts (from another file) from column G if column a b
c and d meet certain requirements a has to match name, c has to match
month, d has to match year. "B" can't equal "SE" or "EQ" or "LABR" or
"SH" or "SOSE" or "SOEQ or "SOCH" which is nine nested if's
this file contains all the lines from every invoice I sent out so the file
can have up to 5000 rows and they are not in any order which is why vlookup
didn't work.

I went to the suggested web site for naming formulas I can't seem to write
it cause it is not working for me but the idea seems as it should work but...
can you take named formulas from one file to another I have 125 files
(clients) that use these fomulas also the web site delt with IF only, i
need to sum if
everyone's help is great

thank you
karen

Frank Kabel said:
Hi
have you tried the suggested formulas? If yes what went wrong with
them?

--
Regards
Frank Kabel
Frankfurt, Germany

I have a work sheet (current.xls) with 8 columns 1(A) is client name
2(B) the item sold 3(C) month it was sold in 4(D) year sold 7(G)
dollar amount

each client has his own file and I am adding the sales up for each
month and broken down by item sold in groups, equipment, shipping,
service and the largest chemical which in its self can have up to 300
different items that was why I did a sum IF <> the small groups but I
added more small groups, service other than contract-SOSE
equiptment other than contract-SOEQ

when I want to get all shipping charges my formula is sum if =$Q$33
the chemical is so large my formula was first intended to add all
except shipping charge(<>$Q$33) except service (<>$Q$31)

I hope this didn't make my question less able to understand
thank you for all your in put

karen
karen said:
this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data
i now have a
need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
I need to add <>Q35,<>Q36,<>Q37
Q30 is a client name
Q29 is a 4 for current year
Q31 tru Q37 are some product codes EX: SE, EQ, LABR,SH...

Thank you for reading this
karen
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
post the formula you have used :)

--
Regards
Frank Kabel
Frankfurt, Germany

karen said:
just tried the sumproduct didn't work get #invalid
I am adding all the amounts (from another file) from column G if column a b
c and d meet certain requirements a has to match name, c has to match
month, d has to match year. "B" can't equal "SE" or "EQ" or "LABR" or
"SH" or "SOSE" or "SOEQ or "SOCH" which is nine nested if's
this file contains all the lines from every invoice I sent out so the file
can have up to 5000 rows and they are not in any order which is why vlookup
didn't work.

I went to the suggested web site for naming formulas I can't seem to write
it cause it is not working for me but the idea seems as it should work but...
can you take named formulas from one file to another I have 125 files
(clients) that use these fomulas also the web site delt with IF only, i
need to sum if
everyone's help is great

thank you
karen

Frank Kabel said:
Hi
have you tried the suggested formulas? If yes what went wrong with
them?

--
Regards
Frank Kabel
Frankfurt, Germany

I have a work sheet (current.xls) with 8 columns 1(A) is client name
2(B) the item sold 3(C) month it was sold in 4(D) year sold 7(G)
dollar amount

each client has his own file and I am adding the sales up for each
month and broken down by item sold in groups, equipment, shipping,
service and the largest chemical which in its self can have up to 300
different items that was why I did a sum IF <> the small groups but I
added more small groups, service other than contract-SOSE
equiptment other than contract-SOEQ

when I want to get all shipping charges my formula is sum if =$Q$33
the chemical is so large my formula was first intended to add all
except shipping charge(<>$Q$33) except service (<>$Q$31)

I hope this didn't make my question less able to understand
thank you for all your in put

karen
:

this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data
i now have a
need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
I need to add <>Q35,<>Q36,<>Q37
Q30 is a client name
Q29 is a 4 for current year
Q31 tru Q37 are some product codes EX: SE, EQ, LABR,SH...

Thank you for reading this
karen
Click to expand...
Click to expand...
Click to expand...
 
K

karen

=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

a new one also needs to include Q35 and Q36 and maybe Q37



Frank Kabel said:
Hi
post the formula you have used :)

--
Regards
Frank Kabel
Frankfurt, Germany

karen said:
just tried the sumproduct didn't work get #invalid
I am adding all the amounts (from another file) from column G if column a b
c and d meet certain requirements a has to match name, c has to match
month, d has to match year. "B" can't equal "SE" or "EQ" or "LABR" or
"SH" or "SOSE" or "SOEQ or "SOCH" which is nine nested if's
this file contains all the lines from every invoice I sent out so the file
can have up to 5000 rows and they are not in any order which is why vlookup
didn't work.

I went to the suggested web site for naming formulas I can't seem to write
it cause it is not working for me but the idea seems as it should work but...
can you take named formulas from one file to another I have 125 files
(clients) that use these fomulas also the web site delt with IF only, i
need to sum if
everyone's help is great

thank you
karen

Frank Kabel said:
Hi
have you tried the suggested formulas? If yes what went wrong with
them?

--
Regards
Frank Kabel
Frankfurt, Germany


karen wrote:
I have a work sheet (current.xls) with 8 columns 1(A) is client name
2(B) the item sold 3(C) month it was sold in 4(D) year sold 7(G)
dollar amount

each client has his own file and I am adding the sales up for each
month and broken down by item sold in groups, equipment, shipping,
service and the largest chemical which in its self can have up to 300
different items that was why I did a sum IF <> the small groups but I
added more small groups, service other than contract-SOSE
equiptment other than contract-SOEQ

when I want to get all shipping charges my formula is sum if =$Q$33
the chemical is so large my formula was first intended to add all
except shipping charge(<>$Q$33) except service (<>$Q$31)

I hope this didn't make my question less able to understand
thank you for all your in put

karen
:

this formula works but we are growing and
=SUM(IF('\\Ks2\c\COST\[current.xls]current'!$A$1:$A$10000=$Q$30,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$31,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$33,
IF('\\Ks2\c\COST\[current.xls]current'!$B$1:$B$10000<>$Q$34,
IF('\\Ks2\c\COST\[current.xls]current'!$C$1:$C$10000=G$1,
IF('\\Ks2\c\COST\[current.xls]current'!$D$1:$D$10000=$Q$29,
'\\Ks2\c\COST\[current.xls]current'!$G$1:$G$10000,0),0),0),0),0),0))

this formula has 6 nested items, do to expansion in my costing data
i now have a
need for nine if nests i know it can only handle 7 and am looking
for a new way to look at this problem.
I need to add <>Q35,<>Q36,<>Q37
Q30 is a client name
Q29 is a 4 for current year
Q31 tru Q37 are some product codes EX: SE, EQ, LABR,SH...

Thank you for reading this
karen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fromula nesting problems 1
Formula help please!! IF(OR 2
Finding dynamic maxima 9
Formula help please! 2
what to use 2
Incrementing IPv6 address 1
Need help matching similar data... 4
if function (if more that 7 nesting) 2

Top