Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE using SUMPRODUCT function
I am using the following formula to check for 2 criteria
=SUMPRODUCT(--('Intl Detail'!A2:A737=10950796)*('Intl Detail'!W2:W737="Y")) This gives me the correct count for the information I have. However, on a new spreadsheet (this is on a monthly report we get) I get the #VALUE message for this formula =SUMPRODUCT(--('Intl Detail'!A2:A898=10950796)*('Intl Detail'!W2:W898="Y")) The only difference is the range specified. This happens on my office machine as well as my home machine. Both are using Excel 2003. -- Brooks W. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE using SUMPRODUCT function
Does one of the cells in either A738:A898 or W738:898 have a #VALUE in it?
That woudl cause SUMPRODUCT to return this error. "Brooks W." wrote: I am using the following formula to check for 2 criteria =SUMPRODUCT(--('Intl Detail'!A2:A737=10950796)*('Intl Detail'!W2:W737="Y")) This gives me the correct count for the information I have. However, on a new spreadsheet (this is on a monthly report we get) I get the #VALUE message for this formula =SUMPRODUCT(--('Intl Detail'!A2:A898=10950796)*('Intl Detail'!W2:W898="Y")) The only difference is the range specified. This happens on my office machine as well as my home machine. Both are using Excel 2003. -- Brooks W. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE using SUMPRODUCT function
OH My GOSH. Thanks. I guess I just needed to think differently. I found the
problem and fixed the error. Thank You! -- Brooks W. "Sean Timmons" wrote: Does one of the cells in either A738:A898 or W738:898 have a #VALUE in it? That woudl cause SUMPRODUCT to return this error. "Brooks W." wrote: I am using the following formula to check for 2 criteria =SUMPRODUCT(--('Intl Detail'!A2:A737=10950796)*('Intl Detail'!W2:W737="Y")) This gives me the correct count for the information I have. However, on a new spreadsheet (this is on a monthly report we get) I get the #VALUE message for this formula =SUMPRODUCT(--('Intl Detail'!A2:A898=10950796)*('Intl Detail'!W2:W898="Y")) The only difference is the range specified. This happens on my office machine as well as my home machine. Both are using Excel 2003. -- Brooks W. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE using SUMPRODUCT function
Firstly, the -- in your formula is redundant.
Secondly, it looks as though you have a formula returning an error result in the data. -- __________________________________ HTH Bob "Brooks W." wrote in message ... I am using the following formula to check for 2 criteria =SUMPRODUCT(--('Intl Detail'!A2:A737=10950796)*('Intl Detail'!W2:W737="Y")) This gives me the correct count for the information I have. However, on a new spreadsheet (this is on a monthly report we get) I get the #VALUE message for this formula =SUMPRODUCT(--('Intl Detail'!A2:A898=10950796)*('Intl Detail'!W2:W898="Y")) The only difference is the range specified. This happens on my office machine as well as my home machine. Both are using Excel 2003. -- Brooks W. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct function / VB user defined function | Excel Discussion (Misc queries) | |||
SumProduct function | Excel Worksheet Functions | |||
use of sumproduct function | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
Sumproduct function | Excel Discussion (Misc queries) |