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..

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..

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..

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..

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..