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