Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
Hi google groups?
I have been searching online to find a way to make a count by two criteria - year and Y/N. In all the searching it seems I have to do a sumproduct, and wrote the formula below. When I do countifs for each individual criteria, I get the actual number (e.g. =COUNTIF('Other Worksheet'!$M$4:$M$2000,"Y"), but when I use the sumproduct formula, I receive a #VALUE! error message. I've used the " " for the text. What else could cause this error message? =SUMPRODUCT(('Other Worksheet'!$K$4:$K$2000=1997)*('Other Worksheet'!$M $4:$M$2000="Y")) Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
"SunshineMR" wrote in message
... Hi google groups? I have been searching online to find a way to make a count by two criteria - year and Y/N. In all the searching it seems I have to do a sumproduct, and wrote the formula below. When I do countifs for each individual criteria, I get the actual number (e.g. =COUNTIF('Other Worksheet'!$M$4:$M$2000,"Y"), but when I use the sumproduct formula, I receive a #VALUE! error message. I've used the " " for the text. What else could cause this error message? =SUMPRODUCT(('Other Worksheet'!$K$4:$K$2000=1997)*('Other Worksheet'!$M $4:$M$2000="Y")) Thanks! There's nothing wrong with your formula. Are there any #VALUE! errors in any of the referenced ranges? -- Biff Microsoft Excel MVP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
Try this:
=SUMPRODUCT(N('Other Worksheet'!$K$4:$K$2000=1997)*N('Other Worksheet'!$M$4:$M$2000="Y")) SUMPRODUCT needs numeric arguments. The arguments in the previous version of your formula were an array of TRUE and FALSE values. Using the N funtion these will become an array of ! and ) values. -- Adilson Soledade "T. Valko" wrote: "SunshineMR" wrote in message ... Hi google groups? I have been searching online to find a way to make a count by two criteria - year and Y/N. In all the searching it seems I have to do a sumproduct, and wrote the formula below. When I do countifs for each individual criteria, I get the actual number (e.g. =COUNTIF('Other Worksheet'!$M$4:$M$2000,"Y"), but when I use the sumproduct formula, I receive a #VALUE! error message. I've used the " " for the text. What else could cause this error message? =SUMPRODUCT(('Other Worksheet'!$K$4:$K$2000=1997)*('Other Worksheet'!$M $4:$M$2000="Y")) Thanks! There's nothing wrong with your formula. Are there any #VALUE! errors in any of the referenced ranges? -- Biff Microsoft Excel MVP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
The arguments in the previous version of your
formula were an array of TRUE and FALSE values Using the N funtion these will become an array of ! and ) values. I guess you missed this part of the formula: ....2000=1997)*('Other Worksheet'!... N does nothing that the "*" already doesn't do so the use of the N function is redundant. -- Biff Microsoft Excel MVP "Adilson Soledade" wrote in message ... Try this: =SUMPRODUCT(N('Other Worksheet'!$K$4:$K$2000=1997)*N('Other Worksheet'!$M$4:$M$2000="Y")) SUMPRODUCT needs numeric arguments. The arguments in the previous version of your formula were an array of TRUE and FALSE values. Using the N funtion these will become an array of ! and ) values. -- Adilson Soledade "T. Valko" wrote: "SunshineMR" wrote in message ... Hi google groups? I have been searching online to find a way to make a count by two criteria - year and Y/N. In all the searching it seems I have to do a sumproduct, and wrote the formula below. When I do countifs for each individual criteria, I get the actual number (e.g. =COUNTIF('Other Worksheet'!$M$4:$M$2000,"Y"), but when I use the sumproduct formula, I receive a #VALUE! error message. I've used the " " for the text. What else could cause this error message? =SUMPRODUCT(('Other Worksheet'!$K$4:$K$2000=1997)*('Other Worksheet'!$M $4:$M$2000="Y")) Thanks! There's nothing wrong with your formula. Are there any #VALUE! errors in any of the referenced ranges? -- Biff Microsoft Excel MVP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
I am having a similar problem this morning.
=SUMPRODUCT(('0107'!$E1:$E200="CURRENT")*('0107'! $F1:$F200="VIRTUOSO")*('0107'!$C1:$C200)) It is returning the #VALUE! error. The arguements are all correct and my reference data is also correct on the other sheet. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
So are you going to help other readers of the group by telling them the
cause of your problem? Normally we would have assumed that there was an error in your input values, but you are assuring us that this is not the case, so I am sure that other readers would be delighted to hear your explanation. Just to satisfy our idle curiosity, what do you get from the formula =SUM(--ISTEXT('0107'!C1:C200)) entered as an array formula (Control Shift Enter, which will put curly brackets around the formula)? -- David Biddulph wrote in message ... I am having a similar problem this morning. =SUMPRODUCT(('0107'!$E1:$E200="CURRENT")*('0107'! $F1:$F200="VIRTUOSO")*('0107'!$C1:$C200)) It is returning the #VALUE! error. The arguements are all correct and my reference data is also correct on the other sheet. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Value Error Message
Do you have any text in C1:C200 of the 0107 worksheet?
Do you have any errores in E1:E200 or F1:F200 of that same sheet? Remember to look at headers and hidden (by autofilter???) rows, too. wrote: I am having a similar problem this morning. =SUMPRODUCT(('0107'!$E1:$E200="CURRENT")*('0107'! $F1:$F200="VIRTUOSO")*('0107'!$C1:$C200)) It is returning the #VALUE! error. The arguements are all correct and my reference data is also correct on the other sheet. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error - message help please | Excel Discussion (Misc queries) | |||
VBA Error Message "Compile Error...." | Excel Discussion (Misc queries) | |||
It's me again!! Error message. | Excel Discussion (Misc queries) | |||
changing the message in an error message | Excel Worksheet Functions | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |