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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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


.