SumProduct formula gives me #VALUE! error

[nate777]

Hi Everyone,

I have the below formula entered in a stray cell(H8) outside my data table, to give me all "closed" incidents(COL G) that are more than 90 days old (Col H), but gives me a #VALUE! error.

=SUMPRODUCT((G12:G1000="Closed")*(H12:H1000>90))

This formula works in my other sheets, but not in this one. I checked to see the cell format for data type, it is the same for the one that works and that does n't.

FYI....I have advanced filter applied on the data table.


Not able to figure out. can you please help!


Thanks!
Nate

 

[Richard Schollar]

Hi Nate

Do you have any #VALUE errors in either G12:G1000 or H12:H1000 (and I mean in the currently hidden ones as well)?

Richard

 

[nate777]

Thanks Richard.

I did find #VALUE error in some of my hidden cells.

Here is another problem.

I have a macro recorded to copy(as link) Col H data (above) from the orginal source sheet (eg: Data!H12). In the Orginal source sheet, this is a calculated date item. It counts the # of days between two dates(e.g: H2= J2-G2, where Col J and G are Date columns). I fill down the formula in H2 to other cells. However in cells that are empty, It puts a negative #
(eg: -38371). I tried to remove the negative number, by applying data validation(by selecting whole numbers and minimum value> 0). It does NOT help.

Any suggestions.

 

[Richard Schollar]

Perhaps using an IF statement to return an empty string say if the number will ne negative:

=IF(J2-G2<0,"",J2-G2)

Richard

 

[nate777]

Thanks Richard.It works!