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

you can try this

=SUMPRODUCT(--(U2:U5="X"),--(T2:T5<J4),V2:V5)

--


Gary


"RyanH" wrote in message
...
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

=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

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

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

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

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

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

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

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