sumproduct/sum not working with multiple variables

[Nelson]

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)=2009)*(B8:B23="D03KHLL"),L8:L23)))

however if I add more part numbers like this

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B8: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

 

[Marcelo]

why not,

=SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B8:B23="D03KHLL"),L8:L23)))+SUMproduct(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(B8:B23="D03KJLL"),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)=2009)*(B8:B23=n6),L8:L23)))


--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Nelson" escreveu:

 

[Luke M]

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="D03KILL"))*(L8:L23))

 

[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]

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?

 

[T. Valko]

=SUMPRODUCT((ISNUMBER(A8:A23))*(YEAR(A8:A23)=2009)*((B8:B23="D03KHLL")+(B8:B23="D03KJLL")+(B8:B23="D03KILL"))*(L8:L23))

That won't work because of this:

(YEAR(A8:A23)=2009)

See my reply in your other post.

 

[Luke M]

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

 

[T. Valko]

=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.

 

[Teethless mama]

Try this:

=SUM(IF(ISNUMBER(A8:A23),IF((YEAR(A8:A23)=2009)*(LEFT(B8:B23,3)="DO3"),L8:L23)))

ctrl+shift+enter, not just enter

 

[Teethless mama]

Another...

=SUMPRODUCT(--(TEXT(A8:A23,"yyyy")="2009"),--(LEFT(B8:B23,3)="DO3"),L8:L23)

Just press ENTER

 

[Teethless mama]

Correction:

should be "D03", not "DO3"