Formula Error

[Ken G]

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

 

[Mike H]

Try

=SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1974<0)*($H$35:$H$1974))

Mike

 

[Ken G]

comes back the same # VALUE

Try

=SUMPRODUCT(($X$35:$X1974=$AC$17)*($H$35:$H$1974<0)*($H$35:$H$1974))

Mike

 

[Don Guillett]

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)

 

[Ken G]

Thanks Don...that worked

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
(e-mail address removed)