Prev: Conditional Formatting
Next: can't get excel to print more than one copy of worksheet at atim
From: Joe M. on 20 Jan 2010 11:34
I am using SUMPRODUCT to sum a column on another worksheet. My formula looks
like this:

=SUMPRODUCT((Data!$E$4:$E$10000=$A$1)*(Data!$H$4:$H$10000=$A4)*(Data!$D$4:$D$10000="Y")*(Data!$J$4:$J$10000="Y"))

The # of rows on worksheet DATA changes perodically. When I delete the rows
in DATA and paste in new rows the "to" reference changes to the same as the
"from" reference:

=SUMPRODUCT((Data!$E$4:$E$4=$A$1)*(Data!$H$4:$H$4=$A4)*(Data!$D$4:$D$4="Y")*(Data!$J$4:$J$4="Y"))

What can I do to keep my "to" reference at 10000?

Thanks,
Joe M.


From: Don Guillett on 20 Jan 2010 11:58
I would make defined name ranges
colE
=offset($e$3,1,0,counta($e:$e),1)
etc then just juse colE=

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Joe M." <JoeM(a)discussions.microsoft.com> wrote in message
news:34E57FB5-BE52-469D-91C8-1462FAD935E3(a)microsoft.com...
>I am using SUMPRODUCT to sum a column on another worksheet. My formula
>looks
> like this:
>
> =SUMPRODUCT((Data!$E$4:$E$10000=$A$1)*(Data!$H$4:$H$10000=$A4)*(Data!$D$4:$D$10000="Y")*(Data!$J$4:$J$10000="Y"))
>
> The # of rows on worksheet DATA changes perodically. When I delete the
> rows
> in DATA and paste in new rows the "to" reference changes to the same as
> the
> "from" reference:
>
> =SUMPRODUCT((Data!$E$4:$E$4=$A$1)*(Data!$H$4:$H$4=$A4)*(Data!$D$4:$D$4="Y")*(Data!$J$4:$J$4="Y"))
>
> What can I do to keep my "to" reference at 10000?
>
> Thanks,
> Joe M.
>
>

From: Joe M. on 20 Jan 2010 13:14
I have created name ranges for col E, H & J.

=SUMPRODUCT((ColE=$A$1)*(ColH=$A4)*(ColJ="Y"))

But the result of the formula is now #N/A

What did I do wrong?

Thanks,
Joe M.



"Don Guillett" wrote:

> I would make defined name ranges
> colE
> =offset($e$3,1,0,counta($e:$e),1)
> etc then just juse colE=
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "Joe M." <JoeM(a)discussions.microsoft.com> wrote in message
> news:34E57FB5-BE52-469D-91C8-1462FAD935E3(a)microsoft.com...
> >I am using SUMPRODUCT to sum a column on another worksheet. My formula
> >looks
> > like this:
> >
> > =SUMPRODUCT((Data!$E$4:$E$10000=$A$1)*(Data!$H$4:$H$10000=$A4)*(Data!$D$4:$D$10000="Y")*(Data!$J$4:$J$10000="Y"))
> >
> > The # of rows on worksheet DATA changes perodically. When I delete the
> > rows
> > in DATA and paste in new rows the "to" reference changes to the same as
> > the
> > "from" reference:
> >
> > =SUMPRODUCT((Data!$E$4:$E$4=$A$1)*(Data!$H$4:$H$4=$A4)*(Data!$D$4:$D$4="Y")*(Data!$J$4:$J$4="Y"))
> >
> > What can I do to keep my "to" reference at 10000?
> >
> > Thanks,
> > Joe M.
> >
> >
>
> .
>
From: Don Guillett on 20 Jan 2010 14:30
Dunno without seeing what you did or

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Joe M." <JoeM(a)discussions.microsoft.com> wrote in message
news:C4D15313-3390-4238-B19F-5C8171973CFF(a)microsoft.com...
>I have created name ranges for col E, H & J.
>
> =SUMPRODUCT((ColE=$A$1)*(ColH=$A4)*(ColJ="Y"))
>
> But the result of the formula is now #N/A
>
> What did I do wrong?
>
> Thanks,
> Joe M.
>
>
>
> "Don Guillett" wrote:
>
>> I would make defined name ranges
>> colE
>> =offset($e$3,1,0,counta($e:$e),1)
>> etc then just juse colE=
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "Joe M." <JoeM(a)discussions.microsoft.com> wrote in message
>> news:34E57FB5-BE52-469D-91C8-1462FAD935E3(a)microsoft.com...
>> >I am using SUMPRODUCT to sum a column on another worksheet. My formula
>> >looks
>> > like this:
>> >
>> > =SUMPRODUCT((Data!$E$4:$E$10000=$A$1)*(Data!$H$4:$H$10000=$A4)*(Data!$D$4:$D$10000="Y")*(Data!$J$4:$J$10000="Y"))
>> >
>> > The # of rows on worksheet DATA changes perodically. When I delete the
>> > rows
>> > in DATA and paste in new rows the "to" reference changes to the same as
>> > the
>> > "from" reference:
>> >
>> > =SUMPRODUCT((Data!$E$4:$E$4=$A$1)*(Data!$H$4:$H$4=$A4)*(Data!$D$4:$D$4="Y")*(Data!$J$4:$J$4="Y"))
>> >
>> > What can I do to keep my "to" reference at 10000?
>> >
>> > Thanks,
>> > Joe M.
>> >
>> >
>>
>> .
>>

 | 
Pages: 1
Prev: Conditional Formatting
Next: can't get excel to print more than one copy of worksheet at atim