linked worksheet formulas

L

Laura Wild

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
 
M

Max

Laura Wild said:
..
Click to expand...
=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
 
B

Biff

Note that the 3 ranges need to be identical

=SUMPRODUCT(--(A1:A10="x"),--(B111:B120>1))

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
 
M

Max

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

Biff

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

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
 
M

Max

Biff said:
.. Believe it or not, I once saw a Bingo card that wasn't symmetric! <g>
Click to expand...

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: [email protected]
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.
 
B

Biff

snip>>>>>>>>>>>>>>>>>

From: "Laura Wild"
To: [email protected]
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
 
Top