How to use SUMPRODUCT or SUMIF with 2 criteria?

[RyanH]

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!

 

[Gary Keramidas]

you can try this

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

 

[Joel]

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

 

[RyanH]

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)

 

[Joel]

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]

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.

 

[Joel]

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.

 

[Gary Keramidas]

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

 

[Bob Phillips]

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

 

[RyanH]

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?