|
sumproduct/sum not working with multiple variables
I am using this formula which works well if I am only trying to match 1 part
number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
why not,
=SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23)))+SUMproduct(IF(IS NUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B8:B23="D03 KJLL"),L8:L23)))+SUMproduct(IF(ISNUMBER(A8:A23),IF ((YEAR(A8:A23)=2009)*(B8:B23="D03KILL"),L8:L23))) or use an auxiliar cell to change the part number so (n6 for instance) =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23=n6),L8:L23))) -- pleae click yes if it was helpfull regards from Brazil Marcelo "Nelson" escreveu: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
I rearranged this to form a better SUMPRODUCT function. If we add the
multiple conditions together, we create the correct array of 1's and 0's that we want to multiply against values you desire (L8:L23), and then correctly sum them up. =SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009) *((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D 03KILL"))*(L8:L23)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
Thanks, error on my part for this though I need to be able to have this
criteria against 25 part numbers which seems to exceed the limitations of excel, I even tried using a wild card for all my part numbers that start with E03* and that does not seem to work. Any suggestions would be great Thanks in advance -- Nelson "Marcelo" wrote: why not, =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23)))+SUMproduct(IF(IS NUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B8:B23="D03 KJLL"),L8:L23)))+SUMproduct(IF(ISNUMBER(A8:A23),IF ((YEAR(A8:A23)=2009)*(B8:B23="D03KILL"),L8:L23))) or use an auxiliar cell to change the part number so (n6 for instance) =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23=n6),L8:L23))) -- pleae click yes if it was helpfull regards from Brazil Marcelo "Nelson" escreveu: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
Thanks Luke, this actually let me use all the part numbers but there appears
to be something wrong with the formula, just by taking what you have here I am getting a "#Value" Any suggestions? -- Nelson "Luke M" wrote: I rearranged this to form a better SUMPRODUCT function. If we add the multiple conditions together, we create the correct array of 1's and 0's that we want to multiply against values you desire (L8:L23), and then correctly sum them up. =SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009) *((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D 03KILL"))*(L8:L23)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
=SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009) *((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D 03KILL"))*(L8:L23))
That won't work because of this: (YEAR(A8:A23)=2009) See my reply in your other post. -- Biff Microsoft Excel MVP "Nelson" wrote in message ... Thanks Luke, this actually let me use all the part numbers but there appears to be something wrong with the formula, just by taking what you have here I am getting a "#Value" Any suggestions? -- Nelson "Luke M" wrote: I rearranged this to form a better SUMPRODUCT function. If we add the multiple conditions together, we create the correct array of 1's and 0's that we want to multiply against values you desire (L8:L23), and then correctly sum them up. =SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009) *((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D 03KILL"))*(L8:L23)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
I just noticed your are using the YEAR function. Unfortunately, if you
evaluate a text with YEAR, it creates an error that carries throughout the formula. As you mentioned you might be able to use wildcards in your other post, perhaps this array* formula will work? =SUM(IF(ISNUMBER(A8:A23),IF(AND(YEAR(A8:A23)=2009, ISNUMBER(SEARCH("D03",B8:B23))),L8:L23))) *Use Ctrl+Shift+Enter to confirm formula, not just Enter -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: Thanks Luke, this actually let me use all the part numbers but there appears to be something wrong with the formula, just by taking what you have here I am getting a "#Value" Any suggestions? -- Nelson "Luke M" wrote: I rearranged this to form a better SUMPRODUCT function. If we add the multiple conditions together, we create the correct array of 1's and 0's that we want to multiply against values you desire (L8:L23), and then correctly sum them up. =SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009) *((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D 03KILL"))*(L8:L23)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
=SUM(IF(ISNUMBER(A8:A23),IF(AND(YEAR(A8:A23)=2009 ,ISNUMBER(SEARCH("D03",B8:B23))),L8:L23)))
That won't work either. It'll still choke on this: YEAR(A8:A23)=2009 Also, AND returns a single element, not an array. See my reply in the original post. -- Biff Microsoft Excel MVP "Luke M" wrote in message ... I just noticed your are using the YEAR function. Unfortunately, if you evaluate a text with YEAR, it creates an error that carries throughout the formula. As you mentioned you might be able to use wildcards in your other post, perhaps this array* formula will work? =SUM(IF(ISNUMBER(A8:A23),IF(AND(YEAR(A8:A23)=2009, ISNUMBER(SEARCH("D03",B8:B23))),L8:L23))) *Use Ctrl+Shift+Enter to confirm formula, not just Enter -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: Thanks Luke, this actually let me use all the part numbers but there appears to be something wrong with the formula, just by taking what you have here I am getting a "#Value" Any suggestions? -- Nelson "Luke M" wrote: I rearranged this to form a better SUMPRODUCT function. If we add the multiple conditions together, we create the correct array of 1's and 0's that we want to multiply against values you desire (L8:L23), and then correctly sum them up. =SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009) *((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D 03KILL"))*(L8:L23)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
Try this:
=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(L EFT(B8:B23,3)="DO3"),L8:L23))) ctrl+shift+enter, not just enter "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
Another...
=SUMPRODUCT(--(TEXT(A8:A23,"yyyy")="2009"),--(LEFT(B8:B23,3)="DO3"),L8:L23) Just press ENTER "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
sumproduct/sum not working with multiple variables
Correction:
should be "D03", not "DO3" "Nelson" wrote: I am using this formula which works well if I am only trying to match 1 part number D03KHLL =SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2 009)*(B8:B23="D03KHLL"),L8:L23))) however if I add more part numbers like this =SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B 8:B23="D03KHLL,D03KJLL,D03KILL"),L8:L23))) this does not seem to be working they way I have read this to work both in sum and sumproduct Any suggestions Thanks -- Nelson |
| All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com