| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| look for a value with multiple variables | Excel Discussion (Misc queries) | |||
| How do I string formulas together in Excel to display variables | New Users to Excel | |||
| Using formulas to source a value based on 2 variables | Excel Worksheet Functions | |||
| How can I use variables in formulas in VB? | Excel Discussion (Misc queries) | |||
| Sumif with multiple variables | Excel Discussion (Misc queries) | |||
Linear Mode
