Using Sumproduct and Dates

[googley]

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))))))

 

[T. Valko]

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))

If there are no empty cells in '7-14-07'!AI2:AI65335 then you can eliminate
this array to help make the formula more efficient:

--(ISNUMBER('7-14-07'!AI2:AI65335))

An empty cell in that range would evaluate to being <=H3

 

[googley]

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))

If there are no empty cells in '7-14-07'!AI2:AI65335 then you can eliminate
this array to help make the formula more efficient:

--(ISNUMBER('7-14-07'!AI2:AI65335))

An empty cell in that range would evaluate to being <=H3

--
Biff
Microsoft Excel MVP








- Show quoted text -

Biff,

That worked! Thanks for your Assistance!

 

[T. Valko]

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))

If there are no empty cells in '7-14-07'!AI2:AI65335 then you can
eliminate
this array to help make the formula more efficient:

--(ISNUMBER('7-14-07'!AI2:AI65335))

An empty cell in that range would evaluate to being <=H3

--
Biff
Microsoft Excel MVP








- Show quoted text -

That worked! Thanks for your Assistance!

You're welcome. Thanks for the feedback!

 

[aalbery]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Bif,

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
W
WI
WIM
WIRD
WIRDM
WIRT
WIRTM
WM
WPE
WPEM
WPI
WPK
WRD
WRDM
WRT
WRTM
Is there a way to do the following with your formula? =SUMPRODUCT(--
(ISNUMBER(FIND({"-W0","-W1","-W2"},'7-14-07'!$E$1:$E$65535)))) this
formula works when just using sumproduct but it has not been fail safe
because the numbers listed to the right of the W can be from 0 to 9.
See my dilemma.

 

[Bob Phillips]

You could use

=SUMPRODUCT(--(ISNUMBER(FIND({"W0","W1","W2","W3","W4","W5","W6","W7","W8","W9"},$E$1:$E$65535))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Bif,

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
W
WI
WIM
WIRD
WIRDM
WIRT
WIRTM
WM
WPE
WPEM
WPI
WPK
WRD
WRDM
WRT
WRTM
Is there a way to do the following with your formula? =SUMPRODUCT(--
(ISNUMBER(FIND({"-W0","-W1","-W2"},'7-14-07'!$E$1:$E$65535)))) this
formula works when just using sumproduct but it has not been fail safe
because the numbers listed to the right of the W can be from 0 to 9.
See my dilemma.

 

[aalbery]

You could use

=SUMPRODUCT(--(ISNUMBER(FIND({"W0","W1","W2","W3","W4","W5","W6","W7","W8",­"W9"},$E$1:$E$65535))))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)






Bif,

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
W
WI
WIM
WIRD
WIRDM
WIRT
WIRTM
WM
WPE
WPEM
WPI
WPK
WRD
WRDM
WRT
WRTM
Is there a way to do the following with your formula? =SUMPRODUCT(--
(ISNUMBER(FIND({"-W0","-W1","-W2"},'7-14-07'!$E$1:$E$65535)))) this
formula works when just using sumproduct but it has not been fail safe
because the numbers listed to the right of the W can be from 0 to 9.
See my dilemma.- Hide quoted text -

- Show quoted text -

Bif,

I wasn't clear this is the formula I am currently using
=SUMPRODUCT(--(ISNUMBER(FIND("SPL",'7-14-07'!$E$2:$E$65535))),--
(ISNUMBER('7-14-07'!$Y$2:$Y$65535)),--('7-14-07'!$Y$2:$Y$65535<=$F$3))