named Ranges question

[John]

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

 

[Arvi Laanemets]

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]

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

 

[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

 

[RagDyer]

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!
==============================================

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