you can try this with dynamic ranges:

insert /name /define
datelist (or choose whatever name you want)
refers to:
=OFFSET(Sheet1!$T$2,0,0,COUNTA(Sheet1!$T:$T),1)
then add it

valuelist

refers to:
=OFFSET(Sheet1!$V$2,0,0,COUNTA(Sheet1!$V:$V),1)
then add it

xlist

refers to:
=OFFSET(Sheet1!$T$2,0,1,COUNTA(Sheet1!$T:$T),1)
then add it

now for the formula:
=SUMPRODUCT(--(xlist="X"),--(datelist<J4),valuelist)

if you need help with dynamic ranges, visit debra's site
http://www.contextures.com/xlNames01.html
--


Gary


"RyanH" wrote in message
...
I'm not sure if I can specify an exact range, because I am always adding and
removing rows. For example, I may have 300 today and 350 tomorrow. Is there
a way to make this function dynamic? Why does SUMPRODUCT not like entire
columns? The function seems to work fine for me.
--
Cheers,
Ryan


"Joel" wrote:

Sumproduct doesn't like specifying the entire column using U:U. You need to
specify a range with all the ranges the same size.

The -- converts an arrays of True's and False's to 1's and 0's.

This statment belwo evaluates to (True,False,True,False,........) adding
the -- gives you (1,0,1,0,....) Sumproduct doesn't know how to add Trues and
Falses.
'Global Schedule'!U:U="X"

You can see what really is happening by using the menu Tool - Formula
Auditing - Evaluate Formula. Select the cell with the formula before running
Evaluate formula.

"RyanH" wrote:

This is what worked for me:

=SUMPRODUCT(--('Global Schedule'!T:T<=$J$4),--('Global
Schedule'!U:U="X"),'Global Schedule'!V:V)

1.) What are the two minus for?

2.) If I wanted to take this function one step further and sum Col.V if
Col.U has an "X", and is between dates in I5 and J5 then I would do this,
right?

=SUMPRODUCT(--('Global Schedule'!T:T=$I$5),--('Global
Schedule'!T:T<=$J$5)--('Global Schedule'!U:U="X"),'Global Schedule'!V:V)

--
Cheers,
Ryan


"Joel" wrote:

=sumproduct(--(Summary!$J$4=T1:T4),--(U1:U4="X"),(V1:V4))

"RyanH" wrote:

I think something like this has been posted several times, but I can't
seem
to get it to work with my application. I need to sum all cells in Col.V
if
the adjacent cell in Col.U has a "X" in it AND if the date in Col.T is
less
than the date in Summary!$J$4. For example, if Summary!$J$4 = 10/15/08
and
using the following data the function should return, 7.00.

The data is in a sheet named "Global Schedule"
Col.T Col.U Col.V
9/15/08 X 2.00
9/22/08 3.50
10/1/08 1.50
10/3/08 X 5.00

Thanks in Advance!
--
Cheers,
Ryan