|
SUMPRODUCT Works Sometimes Why
I have use this formula before, but i try it again on another sheet and now
it does not work. This Works: =SUMPRODUCT(('Dec Actual'!$F$2:$F$100="0103")*('Dec Actual'!$G$2:$G$100=B101)*('Dec Actual'!$I$2:$I$100)) This does not: =SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B35 0=A8)*('Source'!AM5:AM350)) |
I keep geting #VALUE!
"Don Guillett" wrote: it does not work. Means??? -- Don Guillett SalesAid Software "Mestrella31" wrote in message ... I have use this formula before, but i try it again on another sheet and now it does not work. This Works: =SUMPRODUCT(('Dec Actual'!$F$2:$F$100="0103")*('Dec Actual'!$G$2:$G$100=B101)*('Dec Actual'!$I$2:$I$100)) This does not: =SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B35 0=A8)*('Source'!AM5:AM350) ) |
2 Attachment(s)
from HELP for Error
Correct a #VALUE! error Occurs when the wrong type of argument or operand is used. 1.. Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears. 2.. Review the possible causes and solutions. Possible causes and solutions Entering text when the formula requires a number or a logical value, such as TRUE or FALSE Microsoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!. Entering or editing an array formula, and then pressing ENTER Select the cell or range of cells that contains the array formula, press F2 to edit the formula, and then press CTRL+SHIFT+ENTER. Entering a cell reference, a formula, or a function as an array constant Make sure the array constant is not a cell reference, formula, or function. Supplying a range to an operator or a function that requires a single value, not a range a.. Change the range to a single value. b.. Change the range to include either the same row or the same column that contains the formula. Using a matrix that is not valid in one of the matrix worksheet functions Make sure the dimensions of the matrix are correct for the matrix arguments. Running a macro that enters a function that returns #VALUE! Make sure the function is not using an incorrect argument. -- Don Guillett SalesAid Software "Mestrella31" wrote in message ... I keep geting #VALUE! "Don Guillett" wrote: it does not work. Means??? -- Don Guillett SalesAid Software "Mestrella31" wrote in message ... I have use this formula before, but i try it again on another sheet and now it does not work. This Works: =SUMPRODUCT(('Dec Actual'!$F$2:$F$100="0103")*('Dec Actual'!$G$2:$G$100=B101)*('Dec Actual'!$I$2:$I$100)) This does not: =SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B35 0=A8)*('Source'!AM5:AM350) ) |
=SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B35 0=A8)*('Source'!AM5:AM350))
would return #VALUE! when you have text (including formula blanks) in 'Source'!AM5:AM350. Switch to the comma syntax... =SUMPRODUCT(--('Source'!A5:A350="A"),--(Source'!B5:B350=A8),'Source'!AM5:AM350) =SUMPRODUCT(('Source'!A5:A350="A")+0,(Source'!B5:B 350=A8)+0,'Source'!AM5:AM350) =SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B35 0=A8),'Source'!AM5:AM350) Mestrella31 wrote: I keep geting #VALUE! "Don Guillett" wrote: it does not work. Means??? -- Don Guillett SalesAid Software "Mestrella31" wrote in message ... I have use this formula before, but i try it again on another sheet and now it does not work. This Works: =SUMPRODUCT(('Dec Actual'!$F$2:$F$100="0103")*('Dec Actual'!$G$2:$G$100=B101)*('Dec Actual'!$I$2:$I$100)) This does not: =SUMPRODUCT(('Source'!A5:A350="A")*(Source'!B5:B 350=A8)*('Source'!AM5:AM350) ) |
| All times are GMT +1. The time now is 07:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com