| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Error
=SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1975<0 ),$H$35:$H$1975)
This formula comes up with #VALUE error What I am trying to do: Cell AC17 = 2 Col X has a manual input data...say "2"..it looks in col X from row 35 to 1974.. for example..see all the "2" in that row.. looks in Col. H.(same range)..to see if that number is less than 0 (<0).. if it is ..it takes that number and add to current value.. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
Try
=SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1974<0 )*($H$35:$H$1974)) Mike "Ken G" wrote: =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1975<0 ),$H$35:$H$1975) This formula comes up with #VALUE error What I am trying to do: Cell AC17 = 2 Col X has a manual input data...say "2"..it looks in col X from row 35 to 1974.. for example..see all the "2" in that row.. looks in Col. H.(same range)..to see if that number is less than 0 (<0).. if it is ..it takes that number and add to current value.. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
comes back the same # VALUE
"Mike H" wrote: Try =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1974<0 )*($H$35:$H$1974)) Mike "Ken G" wrote: =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1975<0 ),$H$35:$H$1975) This formula comes up with #VALUE error What I am trying to do: Cell AC17 = 2 Col X has a manual input data...say "2"..it looks in col X from row 35 to 1974.. for example..see all the "2" in that row.. looks in Col. H.(same range)..to see if that number is less than 0 (<0).. if it is ..it takes that number and add to current value.. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
Try it this way, Are you SURE you have the same numbers for the ranges?????
1974 1975 =SUMPRODUCT(--($X$35:$X1974=$AC$17)*--($H$35:$H$1974<0),$H$35:$H$1974) -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken G" wrote in message ... comes back the same # VALUE "Mike H" wrote: Try =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1974<0 )*($H$35:$H$1974)) Mike "Ken G" wrote: =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1975<0 ),$H$35:$H$1975) This formula comes up with #VALUE error What I am trying to do: Cell AC17 = 2 Col X has a manual input data...say "2"..it looks in col X from row 35 to 1974.. for example..see all the "2" in that row.. looks in Col. H.(same range)..to see if that number is less than 0 (<0).. if it is ..it takes that number and add to current value.. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
Thanks Don...that worked
"Don Guillett" wrote: Try it this way, Are you SURE you have the same numbers for the ranges????? 1974 1975 =SUMPRODUCT(--($X$35:$X1974=$AC$17)*--($H$35:$H$1974<0),$H$35:$H$1974) -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken G" wrote in message ... comes back the same # VALUE "Mike H" wrote: Try =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1974<0 )*($H$35:$H$1974)) Mike "Ken G" wrote: =SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1975<0 ),$H$35:$H$1975) This formula comes up with #VALUE error What I am trying to do: Cell AC17 = 2 Col X has a manual input data...say "2"..it looks in col X from row 35 to 1974.. for example..see all the "2" in that row.. looks in Col. H.(same range)..to see if that number is less than 0 (<0).. if it is ..it takes that number and add to current value.. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula returns error; abbreviating the formula | Excel Discussion (Misc queries) | |||
| Formula Error-Error Message | Excel Programming | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
| How do I replace "#N/A" error, to continue my formula w/o error? | Excel Worksheet Functions | |||
| Formula error with Mac resulting in '#NAME' error | Excel Programming | |||
Linear Mode
