| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple conditions in formula
I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [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 conditions in formula
You need to be specific with the dept cell, not a whole column
=SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P6)*('Labor Download'!J2:J500)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ang" wrote in message ... I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [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 conditions in formula
Oh, yes, that is part of my problem! I changed the formula accordingly and
now I just get a result of 0 even though there should be a number greater than 0. Do you have any other suggestions? Thx! "Bob Phillips" wrote: You need to be specific with the dept cell, not a whole column =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P6)*('Labor Download'!J2:J500)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ang" wrote in message ... I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [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 conditions in formula
"Ang" wrote:
... I changed the formula accordingly and now I just get a result of 0 even though there should be a number greater than 0. Then the problem's in the data consistency: Either 1. the dates to be matched are not real dates, and/or 2. the text (dept) to be matched may contain extraneous white spaces Let's try shackling #2 which can be overcome with TRIM: =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*(TRIM('Labor Download'!I2:I500)=TRIM(Report!P6))*('Labor Download'!J2:J500)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
Hi Max -
I'm at my wit's end! Need your help again. I fixed my error on this formula and it worked just fine last week. However, now when I open the spreadsheet, nothing's being calculated and I have all zeros. I haven't changed any formulas, links, or ranges - it just doesn't work now. Any ideas? I'm stressed over this and need to figure it out! Do you have any better idea to get what I'm trying to acheive? Thx much for your help! -Ang "Max" wrote: "Ang" wrote: ... I changed the formula accordingly and now I just get a result of 0 even though there should be a number greater than 0. Then the problem's in the data consistency: Either 1. the dates to be matched are not real dates, and/or 2. the text (dept) to be matched may contain extraneous white spaces Let's try shackling #2 which can be overcome with TRIM: =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*(TRIM('Labor Download'!I2:I500)=TRIM(Report!P6))*('Labor Download'!J2:J500)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
Perhaps the book's calc mode was inadvertently toggled to manual?
Click Tools Options Calculation tab Check "Auto" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Ang" wrote: Hi Max - I'm at my wit's end! Need your help again. I fixed my error on this formula and it worked just fine last week. However, now when I open the spreadsheet, nothing's being calculated and I have all zeros. I haven't changed any formulas, links, or ranges - it just doesn't work now. Any ideas? I'm stressed over this and need to figure it out! Do you have any better idea to get what I'm trying to acheive? Thx much for your help! -Ang |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
Hi Bob -
Need your help again. I fixed my error on this formula and it worked just fine last week. However, now when I open the spreadsheet, nothing's being calculated and I have all zeros. I haven't changed any formulas, links, or ranges - it just doesn't work now. Any ideas? I'm stressed over this and need to figure it out! Do you have any better idea to get what I'm trying to acheive? Thx much for your help! -Ang "Bob Phillips" wrote: You need to be specific with the dept cell, not a whole column =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P6)*('Labor Download'!J2:J500)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ang" wrote in message ... I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [where Report!A6=date and Report!P=dept and labor downloadJ=# hours] but the result is #NUM! Clear as mud? Thx much! -Angela |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
From what I can tell just by looking at your formula this is the part
that is causing problems: ('LaborDownload'!I2:I500=Report!P:P) I don't think you can use the whole column as a condition. This should work: ('LaborDownload'!I2:I500=Report!p2) Best regards Per Erik Ang wrote: I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [where Report!A6=date and Report!P=dept and labor downloadJ=# hours] but the result is #NUM! Clear as mud? Thx much! -Angela |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
Hi Per-
Yes, that is part of my problem! I changed the formula but now I get a result of 0 which shouldn't be the case. Any suggestions? I'm confused! Mange takk! -Angela "Per Erik Midtrød" wrote: From what I can tell just by looking at your formula this is the part that is causing problems: ('LaborDownload'!I2:I500=Report!P:P) I don't think you can use the whole column as a condition. This should work: ('LaborDownload'!I2:I500=Report!p2) Best regards Per Erik Ang wrote: I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [where Report!A6=date and Report!P=dept and labor downloadJ=# hours] but the result is #NUM! Clear as mud? Thx much! -Angela |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
=Report!P:P
That reference has to be the same size as the others: =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P2:P500)*('Labor download'!J2:J500)) Note that with SUMPRODUCT you can't (directly) use entire columns as range references (unless you're using Excel 2007). Biff "Ang" wrote in message ... I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [where Report!A6=date and Report!P=dept and labor downloadJ=# hours] but the result is #NUM! Clear as mud? Thx much! -Angela |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
Yes, silly me! That is part of the problem. I just changed the formula but
get results of 0 for the entire column when it should be greater than 0. Any other suggestions? Thx! -Angela "T. Valko" wrote: =Report!P:P That reference has to be the same size as the others: =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P2:P500)*('Labor download'!J2:J500)) Note that with SUMPRODUCT you can't (directly) use entire columns as range references (unless you're using Excel 2007). Biff "Ang" wrote in message ... I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [where Report!A6=date and Report!P=dept and labor downloadJ=# hours] but the result is #NUM! Clear as mud? Thx much! -Angela |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple conditions in formula
Need your help again. I fixed my error on this formula and it worked just fine last week. However, now when I open the spreadsheet, nothing's being calculated and I have all zeros. I haven't changed any formulas, links, or ranges - it just doesn't work now. Any ideas? I'm stressed over this and need to figure it out! Do you have any better idea to get what I'm trying to acheive? Thx much for your help! -Ang "T. Valko" wrote: =Report!P:P That reference has to be the same size as the others: =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P2:P500)*('Labor download'!J2:J500)) Note that with SUMPRODUCT you can't (directly) use entire columns as range references (unless you're using Excel 2007). Biff "Ang" wrote in message ... I'm desparate! 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 tried =SUMPRODUCT(('Labor Download'!O2:O500=Report!A6)*('Labor Download'!I2:I500=Report!P:P)*('Labor Download'!J2:J500)) [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 | |||
| How do I add multiple conditions to a formula? | Excel Worksheet Functions | |||
| Need help on formula with multiple conditions... | Excel Discussion (Misc queries) | |||
| Multiple conditions in a formula | Excel Worksheet Functions | |||
| How do I set up a formula with multiple conditions? | Excel Discussion (Misc queries) | |||
| Formula for multiple conditions | Excel Discussion (Misc queries) | |||
Hybrid Mode
