Multiple conditions in formula

A

Ang

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
 
P

Per Erik Midtrød

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
 
B

Bob Phillips

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

T. Valko

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

Ang

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 said:
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 said:
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
Click to expand...
Click to expand...
 
A

Ang

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 said:
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
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
Click to expand...
Click to expand...
 
A

Ang

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 said:
=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 said:
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
Click to expand...
Click to expand...
 
M

Max

Ang said:
... I changed the formula accordingly and
now I just get a result of 0 even though
there should be a number greater than 0.
Click to expand...

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

Ang

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 said:
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 said:
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
Click to expand...
Click to expand...
 
A

Ang

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 said:
=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 said:
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
Click to expand...
Click to expand...
 
A

Ang

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
 
M

Max

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

Max

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...
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

multiple variables in sumproduct or if/then formulas 4
Multiple variables-SOS 3
Conditional formatting with multiple formula 2
Hyperlink imported data into worksheet with dated formate 0
SUMPRODUCT formula compare then count 3
Multiple lookup values and adding multiple rates across together 5
Sorting with Conditions-This is a tough one 4
Search multiple cells with conditions, sum and auto populate! 3

Top