If it works ok for you then great. I'm using excel 2003 wheree it gives me
problems. Maye this is something microsoft fixed. You could make it 1000
rows (larger than you ever use). Where the number of rows are 65536 you
could make it 65530.

"RyanH" wrote:

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