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

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

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

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

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((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

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

=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

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

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

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