Sumproduct & Dates & Multiple similar alpha Data

[aalbery]

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!

 

[Ragdyer]

What exactly are *all* the descriptions that you're looking to count at one
time?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
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!

 

[Googley]

What exactly are *all* the descriptions that you're looking to count at one
time?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !

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!

Rd,

There are multiples of each type like
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G002

and so on I need to sum all the "G" instances like the above would be
7.

then if there are multiples of
2060207-GL001

i need to be able to sum all the instances with GL

There is always a three digits behind each of the designations range
from 001 to 999

right now the formula I have when It searches for "G" it returns the
"G" and "GL" instances

 

[Ragdyer]

Are you looking to count the "G" and the "Gx" and the "Gxx" together, at the
same time, to get a single count of those 3 types of occurrences?
OR
Are you looking to count the different types separately, to get 3 individual
counts?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

What exactly are *all* the descriptions that you're looking to count at one

time?
--
Regards,

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
[snip]
Rd,

There are multiples of each type like
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G002

and so on I need to sum all the "G" instances like the above would be
7.

then if there are multiples of
2060207-GL001

i need to be able to sum all the instances with GL

There is always a three digits behind each of the designations range
from 001 to 999

right now the formula I have when It searches for "G" it returns the
"G" and "GL" instances

 

[Googley]

Are you looking to count the "G" and the "Gx" and the "Gxx" together, at the
same time, to get a single count of those 3 types of occurrences?
OR
Are you looking to count the different types separately, to get 3 individual
counts?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !

What exactly are *all* the descriptions that you're looking to count at one

time?

RD

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may benefit !
[snip]

Rd,

There are multiples of each type like
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G002

and so on I need to sum all the "G" instances like the above would be
7.

then if there are multiples of
2060207-GL001

i need to be able to sum all the instances with GL

There is always a three digits behind each of the designations range
from 001 to 999

right now the formula I have when It searches for "G" it returns the
"G" and "GL" instances

I am looking to count the different types separately, to get 3
individual
counts

 

[Ragdyer]

In re-reading your posts, I am getting confused by the differences between
your examples and your explanations.

You start off by saying that you want to match 1 to 3 letter designations,
but your examples show up to 5 alphas.
At one point you say that there are 3 digits behind *all* the alpha
designations, and then your examples display 4 alphas with *no* digits!

You must accurately describe your data configuration in order to receive any
worthwhile suggestions, where the responder might spend a great deal of time
on a formula that will not come near to what you actually need and can use.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Are you looking to count the "G" and the "Gx" and the "Gxx" together, at the
same time, to get a single count of those 3 types of occurrences?
OR
Are you looking to count the different types separately, to get 3 individual
counts?
--
Regards,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

What exactly are *all* the descriptions that you're looking to count

at
one

time?

--------------------------------------------------------------------------

-
Please keep all correspondence within the NewsGroup, so all may

benefit !

[snip]

Rd,

There are multiples of each type like
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G001
2060207-G002

and so on I need to sum all the "G" instances like the above would be
7.

then if there are multiples of
2060207-GL001

i need to be able to sum all the instances with GL

There is always a three digits behind each of the designations range
from 001 to 999

right now the formula I have when It searches for "G" it returns the
"G" and "GL" instances

I am looking to count the different types separately, to get 3
individual
counts