multiple variables in sumproduct or if/then formulas
 

Help....I just can't figure out a correct formula. I need a formula that
gives me:
if a specific date spreadsheet A = a date on spreadsheet B
and a specific dept # on spreadsheet A = the department# on spreadsheet B
then get the # of hours for that department on spreadsheet B for that
specific date

I was trying =SUMPRODUCT(('Labor Download'!O:O=Report!A6)*('Labor
Download'!I:I=Report!P:P)*('Labor Download'!J:J)) [where Report!A6=date
and Report!P=dept and labor downloadJ=# hours]
but the result is #NUM!

Clear as mud?
Thx much!
-Angela

multiple variables in sumproduct or if/then formulas
 

Prior to XL2007, Sumproduct cannot use an entire column. Instead of O:O, use
the actual range or use O1:O65535, or use a dynamic named range.

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

"Ang" wrote:

Help....I just can't figure out a correct formula. I need a formula that
gives me:
if a specific date spreadsheet A = a date on spreadsheet B
and a specific dept # on spreadsheet A = the department# on spreadsheet B
then get the # of hours for that department on spreadsheet B for that
specific date

I was trying =SUMPRODUCT(('Labor Download'!O:O=Report!A6)*('Labor
Download'!I:I=Report!P:P)*('Labor Download'!J:J)) [where Report!A6=date
and Report!P=dept and labor downloadJ=# hours]
but the result is #NUM!

Clear as mud?
Thx much!
-Angela

multiple variables in sumproduct or if/then formulas
 

I tried this and still get the #num! error message. Any other ideas? I'm
just plain stuck!
Thx!

"JMB" wrote:

Prior to XL2007, Sumproduct cannot use an entire column. Instead of O:O, use
the actual range or use O1:O65535, or use a dynamic named range.

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

"Ang" wrote:

Help....I just can't figure out a correct formula. I need a formula that
gives me:
if a specific date spreadsheet A = a date on spreadsheet B
and a specific dept # on spreadsheet A = the department# on spreadsheet B
then get the # of hours for that department on spreadsheet B for that
specific date

I was trying =SUMPRODUCT(('Labor Download'!O:O=Report!A6)*('Labor
Download'!I:I=Report!P:P)*('Labor Download'!J:J)) [where Report!A6=date
and Report!P=dept and labor downloadJ=# hours]
but the result is #NUM!

Clear as mud?
Thx much!
-Angela

multiple variables in sumproduct or if/then formulas
 

A little light on details. What exactly did you try?

"Ang" wrote:

I tried this and still get the #num! error message. Any other ideas? I'm
just plain stuck!
Thx!

"JMB" wrote:

Prior to XL2007, Sumproduct cannot use an entire column. Instead of O:O, use
the actual range or use O1:O65535, or use a dynamic named range.

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

"Ang" wrote:

Help....I just can't figure out a correct formula. I need a formula that
gives me:
if a specific date spreadsheet A = a date on spreadsheet B
and a specific dept # on spreadsheet A = the department# on spreadsheet B
then get the # of hours for that department on spreadsheet B for that
specific date

I was trying =SUMPRODUCT(('Labor Download'!O:O=Report!A6)*('Labor
Download'!I:I=Report!P:P)*('Labor Download'!J:J)) [where Report!A6=date
and Report!P=dept and labor downloadJ=# hours]
but the result is #NUM!

Clear as mud?
Thx much!
-Angela

multiple variables in sumproduct or if/then formulas
 

I figure it out! Thank you so much for your help - you saved me!

-Ang

"JMB" wrote:

A little light on details. What exactly did you try?

"Ang" wrote:

I tried this and still get the #num! error message. Any other ideas? I'm
just plain stuck!
Thx!

"JMB" wrote:

Prior to XL2007, Sumproduct cannot use an entire column. Instead of O:O, use
the actual range or use O1:O65535, or use a dynamic named range.

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

"Ang" wrote:

Help....I just can't figure out a correct formula. I need a formula that
gives me:
if a specific date spreadsheet A = a date on spreadsheet B
and a specific dept # on spreadsheet A = the department# on spreadsheet B
then get the # of hours for that department on spreadsheet B for that
specific date

I was trying =SUMPRODUCT(('Labor Download'!O:O=Report!A6)*('Labor
Download'!I:I=Report!P:P)*('Labor Download'!J:J)) [where Report!A6=date
and Report!P=dept and labor downloadJ=# hours]
but the result is #NUM!

Clear as mud?
Thx much!
-Angela