Thread: named Ranges question

Hello,

I defined some named ranges in one worshest of my
Excel workbook. One is called "Application" and one is
called "Date". I wrote a formula within a cell of a
different worksheet to count the instances where
Application equals a certain value and Date equals a
certain value. However, my formula is not working
correctly even though it matches the syntax on the
MIcrosoft Excel tutorial site as well as the syntax I
used in another workbook that is working fine. Any
suggestions?
My formula is:
=SUMPRODUCT((Application,C3)*(Date,B5))
The result in the cell is not a number, but #VALUE!

Thanks!
John

Hi

The ranges used as arguments in SUMPRODUCT function MUST be of same
dimension - i.e. when range Application contains one column with 100 rows,
then range Date must contain one column with 100 rows too. So check the
ranges!

Arvi Laanemets


"John" <jrusso@bdn.com> wrote in message
news:0d8e01c4f4fb$4e319f20$a301280a@phx.gbl...
> Hello,
>
> I defined some named ranges in one worshest of my
> Excel workbook. One is called "Application" and one is
> called "Date". I wrote a formula within a cell of a
> different worksheet to count the instances where
> Application equals a certain value and Date equals a
> certain value. However, my formula is not working
> correctly even though it matches the syntax on the
> MIcrosoft Excel tutorial site as well as the syntax I
> used in another workbook that is working fine. Any
> suggestions?
> My formula is:
> =SUMPRODUCT((Application,C3)*(Date,B5))
> The result in the cell is not a number, but #VALUE!
>
> Thanks!
> John

Hello Arvi,

Thanks so much for the solution. I forgot that I
used this same solution before for a different workbook.
Thanks for saving me headaches.


thanks,
John


>-----Original Message-----
>Hi
>
>The ranges used as arguments in SUMPRODUCT function MUST
be of same
>dimension - i.e. when range Application contains one
column with 100 rows,
>then range Date must contain one column with 100 rows
too. So check the
>ranges!
>
>Arvi Laanemets
>
>
>"John" <jrusso@bdn.com> wrote in message
>news:0d8e01c4f4fb$4e319f20$a301280a@phx.gbl...
>> Hello,
>>
>> I defined some named ranges in one worshest of my
>> Excel workbook. One is called "Application" and one is
>> called "Date". I wrote a formula within a cell of a
>> different worksheet to count the instances where
>> Application equals a certain value and Date equals a
>> certain value. However, my formula is not working
>> correctly even though it matches the syntax on the
>> MIcrosoft Excel tutorial site as well as the syntax I
>> used in another workbook that is working fine. Any
>> suggestions?
>> My formula is:
>> =SUMPRODUCT((Application,C3)*(Date,B5))
>> The result in the cell is not a number, but #VALUE!
>>
>> Thanks!
>> John
>
>
>.
>

Hello Arvi,

Thanks for your help. I have another question.
Since the SumProduct funtion has 2 arguments in my
example, how do I Copy Down for many rows and have the
cell number increment for one argument but not for
another?


Thanks Again.
John


>-----Original Message-----
>Hi
>
>The ranges used as arguments in SUMPRODUCT function MUST
be of same
>dimension - i.e. when range Application contains one
column with 100 rows,
>then range Date must contain one column with 100 rows
too. So check the
>ranges!
>
>Arvi Laanemets
>
>
>"John" <jrusso@bdn.com> wrote in message
>news:0d8e01c4f4fb$4e319f20$a301280a@phx.gbl...
>> Hello,
>>
>> I defined some named ranges in one worshest of my
>> Excel workbook. One is called "Application" and one is
>> called "Date". I wrote a formula within a cell of a
>> different worksheet to count the instances where
>> Application equals a certain value and Date equals a
>> certain value. However, my formula is not working
>> correctly even though it matches the syntax on the
>> MIcrosoft Excel tutorial site as well as the syntax I
>> used in another workbook that is working fine. Any
>> suggestions?
>> My formula is:
>> =SUMPRODUCT((Application,C3)*(Date,B5))
>> The result in the cell is not a number, but #VALUE!
>>
>> Thanks!
>> John
>
>
>.
>

Make the references "Absolute".
Either:
C3
TO
$C$3

Or
B5
To
$B$5

Where the $ sign tells XL to *hold* the cell reference,
Which can be *either*/ *and*, Row or Column,
Depending on if you're copying down or copying across.
$C$3 - $C3 - C$3

Since you mentioned copying down,
C$3 or B$5
would suffice.

Lookup absolute and relative cell references in the HELP files.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"John" <anonymous@discussions.microsoft.com> wrote in message
news:08a401c4f503$e21d8930$a501280a@phx.gbl...
Hello Arvi,

Thanks for your help. I have another question.
Since the SumProduct funtion has 2 arguments in my
example, how do I Copy Down for many rows and have the
cell number increment for one argument but not for
another?


Thanks Again.
John


>-----Original Message-----
>Hi
>
>The ranges used as arguments in SUMPRODUCT function MUST
be of same
>dimension - i.e. when range Application contains one
column with 100 rows,
>then range Date must contain one column with 100 rows
too. So check the
>ranges!
>
>Arvi Laanemets
>
>
>"John" <jrusso@bdn.com> wrote in message
>news:0d8e01c4f4fb$4e319f20$a301280a@phx.gbl...
>> Hello,
>>
>> I defined some named ranges in one worshest of my
>> Excel workbook. One is called "Application" and one is
>> called "Date". I wrote a formula within a cell of a
>> different worksheet to count the instances where
>> Application equals a certain value and Date equals a
>> certain value. However, my formula is not working
>> correctly even though it matches the syntax on the
>> MIcrosoft Excel tutorial site as well as the syntax I
>> used in another workbook that is working fine. Any
>> suggestions?
>> My formula is:
>> =SUMPRODUCT((Application,C3)*(Date,B5))
>> The result in the cell is not a number, but #VALUE!
>>
>> Thanks!
>> John
>
>
>.
>