sumproduct help

[Hoss97]

I know this has been addressed, but I am still having trouble getting my sumproduct formula to work. I am using Excel 2003. In column a, I have order numbers that begin with 4 for stock orders and begin with 5 for custom orders. In column e, I have quantities. In column s, I have an "x" if the order has been assembled. I am trying to get a total for the number of custom assemblies that have been completed. The formula that I am using is listed below, but is returning a value of 0. Any help is appreciated!!

=SUMPRODUCT(('Current Workorder List'!A3:A100="5*")*('Current Workorder List'!S3:S100="x"),'Current Workorder List'!E3:E100)

 

[Jonmo1]

Can't use wildcards like that in Sumproduct...

Try using the LEFT function

=SUMPRODUCT((LEFT('Current Workorder List'!A3:A100,1)="5")*('Current Workorder List'!S3:S100="x"),'Current Workorder List'!E3:E100)

 

[T. Valko]

I know this has been addressed, but I am still having trouble getting my sumproduct formula to work. I am using Excel 2003. In column a, I have order numbers that begin with 4 for stock orders and begin with 5 for custom orders. In column e, I have quantities. In column s, I have an "x" if the order has been assembled. I am trying to get a total for the number of custom assemblies that have been completed. The formula that I am using is listed below, but is returning a value of 0. Any help is appreciated!!

=SUMPRODUCT(('Current Workorder List'!A3:A100="5*")*('Current Workorder List'!S3:S100="x"),'Current Workorder List'!E3:E100)

SUMPRODUCT doesn't support the use of wildcards.

Try it like this:

=SUMPRODUCT(--(LEFT('Current Workorder List'!A3:A100)="5"),--('Current Workorder List'!S3:S100="x"),'Current Workorder List'!E3:E100)

 

[Hoss97]

Thank you both for the quick response!! I tried both formulas, and the both worked. What does the "left" at the beginning accomplish?

 

[T. Valko]

Thank you both for the quick response!! I tried both formulas, and the both worked. What does the "left" at the beginning accomplish?

LEFT returns a specified number of characters from a string starting from the first character (leftmost). For example:

A1 = ABCDEF

=LEFT(A1,3) returns ABC

If the number of characters argument is omitted then it defaults to 1.

=LEFT(A1) returns A

 

[Hoss97]

Thank you very much for the help. That simple addition to the formula worked great!!

 

[T. Valko]

Thank you very much for the help. That simple addition to the formula worked great!!

You're welcome. Thanks for the feedback!