Sumproduct

[Googley]

I am currently using the following formula:

=SUMPRODUCT(--(ISNUMBER(FIND("SPGM",'7-14-07'!$E$2:$E$65535))),--
(ISNUMBER('7-14-07'!$Y$2:$Y$65535)),--('7-14-07'!$Y$2:$Y$65535<=$F$3))

this sumproduct matches product codes from work sheet 7-14-07 column
E:E that have the following formats:

2060207-SPGM021
2060207-SPGM022
2060207-SPGM023
2060207-SPGM024
2060207-SPGM025
2060207-SPGM026
2060207-SPGM027

For these product codes it works fine the problem comes in when I need
to match the following product codes;

2060207-SP021
2060207-SP022
2060207-SP023
2060207-SP024
2060207-SP025
2060207-SP026
2060207-SP027

The first 7 digits of the product code are always the same but the
alpha can be 1,2,3,4,5,or 6 characters in length which are always
followed by a three digit number that ranges from 001 thru 999.

The problem is when you sumproduct a "SP" as in the example directly
above but this returns the sum of all "SP" and "SPM" which is where my
problem lies. The remaining part of the formula functions properly
matching dates that are <= F3. I use this to provide all the different
product codes that have been scheduled for production prior to and
including the last day of the month. Any assistance would be greatly
appreciated!

 

[Guest]

How about adding something into your sumproduct for length of the entries in
column E.

I'm going to put SPGM in A1 for this

=SUMPRODUCT(--(ISNUMBER(FIND(A1),'7-14-07'!$E$2:$E$65535))),--
(ISNUMBER('7-14-07'!$Y$2:$Y$65535)),--('7-14-07'!$Y$2:$Y$65535<=$F$3),
--(LEN('7-14-07'!$E$2:$E$65535)=LEN(A1)+11))

I'm not sure if it'll work, but it's the only thing I can think of to get
around your problem.

HTH,
Barb Reinhardt

 

[Don Guillett]

Modify this idea to suit. Change 15 to 13 for sp. Suggest changing 65535 to
a more meaningful number.
=SUMPRODUCT((LEN($H$2:$H$22)=15)*(ISNUMBER(FIND("SP",$H$2:$H$22))),$I$2:$I$22)

 

[Googley]

Modify this idea to suit. Change 15 to 13 for sp. Suggest changing 65535 to
a more meaningful number.
=SUMPRODUCT((LEN($H$2:$H$22)=15)*(ISNUMBER(FIND("SP",$H$2:$H$22))),$I$2:$I$­22)

Don,

Fantastic! Thank you!

 

[Don Guillett]

Glad to help. Pls TOP post

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Modify this idea to suit. Change 15 to 13 for sp. Suggest changing 65535
to
a more meaningful number.
=SUMPRODUCT((LEN($H$2:$H$22)=15)*(ISNUMBER(FIND("SP",$H$2:$H$22))),$I$2:$I$­22)

Don,

Fantastic! Thank you!

 

[Ragdyer]

From your mouth to God's ears!<bg>

 

[Harlan Grove]

Glad to help. Pls TOP post

....

Or bottom-post. Make Don exercise his fingers.

 

[Don Guillett]

A pox on your house

 

[Harlan Grove]

A pox on your house

Somewhat OT. Have you run out of flawed Excel advice to spew?