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

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

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

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

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

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

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

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
---

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

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

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
---

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

Multiple conditions in formula
 

No such luck....any other ideas why this won't work any longer?

"Max" wrote:

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

Multiple conditions in formula
 

I'm a bit surprised the calc mode wasn't it since you said:
I haven't changed any formulas, links, or ranges
- it just doesn't work now...

I'm out of ideas. Something which worked yesterday doesn't just refuse to
function the next day. It could be back to the data consistency issue again.

Suggest you put in a new posting to seek insights from others. Paste the
actual version of the expression that you're using which worked for you and
which somehow doesn't work anymore, despite there being no changes made
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ang" wrote in message
...
No such luck....any other ideas why this won't work any longer?

"Max" wrote:

Perhaps the book's calc mode was inadvertently toggled to manual?
Click Tools Options Calculation tab Check "Auto" OK