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

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

>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

"Max" <[email protected]> wrote in message
news:[email protected]...
> "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
> --
>
>

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" <[email protected]> wrote in message
news:#[email protected]...
> >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

>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:#[email protected]...
>> >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
>
>

"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: [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.

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

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

"Max" <[email protected]> wrote in message
news:[email protected]...
> "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: [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.
>
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>