linked worksheet formulas
 

I am trying to put a total value into a linked worksheet. This value has a
date condition as well as a description condition. I have tried

=DSUM('cat12-Dec04-Oct-05'!A6:V30550,"LightsOn",'cat12-Dec04-Oct-05'!U5:U5)

and

=SUM(IF(('cat12-Dec04-Oct-05'!A8:A30553="12/1/2004")+('cat12-Dec04-Oct-05'!U8:U30553="Sustain"),('cat12-Dec04-Oct05'!P2:P30547)))
neither work can someone help me?
Thanks Laura Wild
Calgary, AB

linked worksheet formulas
 

"Laura Wild" wrote:

..
=SUM(IF(('cat12-Dec04-Oct-05'!A8:A30553="12/1/2004")+('cat12-Dec04-Oct-05'!U
8:U30553="Sustain"),('cat12-Dec04-Oct05'!P2:P30547)))

Try instead:

=SUMPRODUCT(
('cat12-Dec04-Oct-05'!$A$8:$A$30553= --"12/1/2004")
*('cat12-Dec04-Oct-05'!$U$8:$U$30553="Sustain"),
'cat12-Dec04-Oct-05'!$P$8:$P$30553)

Note that the 3 ranges need to be identical (your col P range was
inconsistent), and a "--" is placed in front of the text date,
viz.: --"12/1/2004", to coerce it into a real date for matching with the
real dates in col A
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

linked worksheet formulas
 

Note that the 3 ranges need to be identical

=SUMPRODUCT(--(A1:A10="x"),--(B111:B1201))

The arrays need to be the same size (and shape depending on what you're
doing)

A8:A30553
P2:P30547

Are the same size.

Biff

"Max" wrote in message
...
"Laura Wild" wrote:

..
=SUM(IF(('cat12-Dec04-Oct-05'!A8:A30553="12/1/2004")+('cat12-Dec04-Oct-05'!U
8:U30553="Sustain"),('cat12-Dec04-Oct05'!P2:P30547)))

Try instead:

=SUMPRODUCT(
('cat12-Dec04-Oct-05'!$A$8:$A$30553= --"12/1/2004")
*('cat12-Dec04-Oct-05'!$U$8:$U$30553="Sustain"),
'cat12-Dec04-Oct-05'!$P$8:$P$30553)

Note that the 3 ranges need to be identical (your col P range was
inconsistent), and a "--" is placed in front of the text date,
viz.: --"12/1/2004", to coerce it into a real date for matching with the
real dates in col A
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

linked worksheet formulas
 

Granted, but think it always helps to have the cols set-up identically,
rather than have supposedly corresponding rows "disjointed" & out-of-sync
from col to col. Much easier to visual check & reconcile. And obviously I
didn't check/recon the size of col P's references in the OP's case.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Biff" wrote in message
...
Note that the 3 ranges need to be identical

=SUMPRODUCT(--(A1:A10="x"),--(B111:B1201))

The arrays need to be the same size (and shape depending on what you're
doing)

A8:A30553
P2:P30547

Are the same size.

Biff

linked worksheet formulas
 

Granted, but think it always helps to have the cols set-up identically,
rather than have supposedly corresponding rows "disjointed" & out-of-sync
from col to col.

I agree, but as you know, some people don't have the same design "talents"
as others.

Believe it or not, I once saw a Bingo card that wasn't symmetric! <g

Biff

"Max" wrote in message
...
Granted, but think it always helps to have the cols set-up identically,
rather than have supposedly corresponding rows "disjointed" & out-of-sync
from col to col. Much easier to visual check & reconcile. And obviously
I
didn't check/recon the size of col P's references in the OP's case.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Biff" wrote in message
...
Note that the 3 ranges need to be identical

=SUMPRODUCT(--(A1:A10="x"),--(B111:B1201))

The arrays need to be the same size (and shape depending on what you're
doing)

A8:A30553
P2:P30547

Are the same size.

Biff

linked worksheet formulas
 

"Biff" wrote:
.. Believe it or not, I once saw a Bingo card that wasn't symmetric! <g

Me too, and I wondered then if the designer wasn't feeling "asymmetric" <g

Anyway, the OP seems quite happy with the response posted earlier,
going by this closure note received (excerpted):

From: "Laura Wild"
To:
Subject: Excel Worksheet help
Date: Fri, 25 Nov 2005 15:50:27 -0700

I just want to let you know I really appreciate the sumproduct advise. It
made me look great. ... well with your help I look awesome. So thank you.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

linked worksheet formulas
 

snip

From: "Laura Wild"
To:
Subject: Excel Worksheet help
Date: Fri, 25 Nov 2005 15:50:27 -0700

I just want to let you know I really appreciate the sumproduct advise. It
made me look great. ... well with your help I look awesome. So thank you.

snip

That's great!

I wish I could get a job working with Excel. I bet I could kick some butt!

Biff

"Max" wrote in message
...
"Biff" wrote:
.. Believe it or not, I once saw a Bingo card that wasn't symmetric! <g

Me too, and I wondered then if the designer wasn't feeling "asymmetric"
<g

Anyway, the OP seems quite happy with the response posted earlier,
going by this closure note received (excerpted):

From: "Laura Wild"
To:
Subject: Excel Worksheet help
Date: Fri, 25 Nov 2005 15:50:27 -0700

I just want to let you know I really appreciate the sumproduct advise. It
made me look great. ... well with your help I look awesome. So thank
you.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--