Defining the Range seems to work fine. Problem is I have to create three
list for all 17 depts. Is this going to create a memory problem and cause
the computers to run kinda slow when my application is open? Would it be
better to create a UDF?
--
Cheers,
Ryan


"Bob Phillips" wrote:

If you have Excel 2007, you can use whole columns with SUMPRODUCT and array
formulae.

--
__________________________________
HTH

Bob

"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