sumproduct/sum not working with multiple variables

  • Thread starter Thread starter Nelson
  • Start date Start date
N

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
 
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:
 
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))
 
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
 
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?
 
=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.
 
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
 
=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.
 
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
 
Another...

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

Just press ENTER
 
Back
Top