=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