sumproduct & dates & similar data
 

Please help!
I am currently working on a workbook that has various part nmbers
(2060207-WPI001) on work sheet 7-14-07 in E:E in which I match a 1 to
3 letter designations as in "WPI". In column AI:AI is the dates which
these pieces are scheduled to cast. I need to place a date in cell H3
of sheet 1 as 8/30/07 and sum the total occurences which are less
than
or equal to the 8/30/07 date.

When I use the following I return the total occurences were WPI is
listed but I need the sum of those with dates less than or equal to
8/30/07 for billing purposes.


=SUMPRODUCT(--(ISNUMBER(FIND({"WPI"},'7-14-07'!$E$2:$E$65335)*(--
('7-14-07'!$AI$2:$AI$65335=DATE(2007,8,30+0))))))


Bill suggested to try this:


H3 = 8/30/2007


=SUMPRODUCT(--(ISNUMBER(FIND("WPI",'7-14-07'!E2:E65335))),--
(ISNUMBER('7-14*-07'!AI2:AI65335)),--('7-14-07'!AI2:AI65335<=H3))


This worked but


Have a couple issues with the following Descriptions in getting exact
sum due to the similarity in the descriptions:


2060207-GL101
2060207-G003
2060207-SPL058
2060207-SPLM066
2060207-W003
2060207-WI103
2060207-WIM207
2060207-WIRD100
2060207-WIRDM001
2060207-WIRT002
2060207-WIRTM003
2060207-WM204
2060207-WPE010
2060207-WPEM011
2060207-WPI013
2060207-WPK
2060207-WRD
2060207-WRDM
2060207-WRT
2060207-WRTM
This was suggested but I could not get it to work with the 7-14-07
worksheet
=SUMPRODUCT(--
(ISNUMBER(FIND({"W0","W1","W2","W3","W4","W5","W6" ,"W7","W8",*"W9"},
$E
$1:$E$65535))))
Any Assistance would be greatly Appreciated!

sumproduct & dates & similar data
 

So, of these:
2060207-GL101
2060207-G003
2060207-SPL058
2060207-SPLM066
2060207-W003
2060207-WI103
2060207-WIM207
2060207-WIRD100
2060207-WIRDM001
2060207-WIRT002
2060207-WIRTM003
2060207-WM204
2060207-WPE010
2060207-WPEM011
2060207-WPI013
2060207-WPK
2060207-WRD
2060207-WRDM
2060207-WRT
2060207-WRTM

which do you want to count, and which not? How do you define 'similarity'.
--
p45cal


"Googley" wrote:

Please help!
I am currently working on a workbook that has various part nmbers
(2060207-WPI001) on work sheet 7-14-07 in E:E in which I match a 1 to
3 letter designations as in "WPI". In column AI:AI is the dates which
these pieces are scheduled to cast. I need to place a date in cell H3
of sheet 1 as 8/30/07 and sum the total occurences which are less
than
or equal to the 8/30/07 date.

When I use the following I return the total occurences were WPI is
listed but I need the sum of those with dates less than or equal to
8/30/07 for billing purposes.


=SUMPRODUCT(--(ISNUMBER(FIND({"WPI"},'7-14-07'!$E$2:$E$65335)*(--
('7-14-07'!$AI$2:$AI$65335=DATE(2007,8,30+0))))))


Bill suggested to try this:


H3 = 8/30/2007


=SUMPRODUCT(--(ISNUMBER(FIND("WPI",'7-14-07'!E2:E65335))),--
(ISNUMBER('7-14Â*-07'!AI2:AI65335)),--('7-14-07'!AI2:AI65335<=H3))


This worked but


Have a couple issues with the following Descriptions in getting exact
sum due to the similarity in the descriptions:


2060207-GL101
2060207-G003
2060207-SPL058
2060207-SPLM066
2060207-W003
2060207-WI103
2060207-WIM207
2060207-WIRD100
2060207-WIRDM001
2060207-WIRT002
2060207-WIRTM003
2060207-WM204
2060207-WPE010
2060207-WPEM011
2060207-WPI013
2060207-WPK
2060207-WRD
2060207-WRDM
2060207-WRT
2060207-WRTM
This was suggested but I could not get it to work with the 7-14-07
worksheet
=SUMPRODUCT(--
(ISNUMBER(FIND({"W0","W1","W2","W3","W4","W5","W6" ,"W7","W8",Â*"W9"},
$E
$1:$E$65535))))
Any Assistance would be greatly Appreciated!