Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Poor Workbook Performance due to Named Ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Worksheet Functions | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
Named Cell Ranges | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |