Sumproduct #value! error

[Humate]

Hi All,

I have searched the boards trying to find an answer to this but with no luck.

My formula is as follows:

=SUMPRODUCT((VALUE($E$11:$E$65435)>=$C13)*(VALUE($E$11:$E$65435)<=$D13)*(VALUE(O$11:O$65435)>0))

This works for most of my data, however this particular column (column 0) contains a formula that must have some errors present - these are hidden with ISERROR, but I think my calculations still finds them.

So my question is, how do I sum column $0 even though it contains #value! in some of those cells?

Many thanks in advance
Humate

 

[Brian from Maui]

Try,

=SUMPRODUCT(--($E$11:$E$65435>=$C13),--($E$11:$E$65435<=$D13),--(O$11:O$65435>0),$O$11:$O$65435)

 

[Humate]

Brian,

Thanks for you reply - I have tried your version and it seems to be working and ignoring the #value! errors.

I no longer get #value! as the result of the calculation, but instead the sum returns 0, however this is not the right answer for my sum.

Any ideas what I am doing wrong? The formats for each range are set to general.

Thanks
Humate

 

[Brian from Maui]

What is the ISERROR formula in O and what kind of data is in E and O? The #VALUE error probably came from text in O.

 

[Humate]

In column O I have a vlookup along the lines of this:

=if(iserror(vlookup($a13,'data!'$a$11:$a$10000,5,false)),"",(vlookup($a13,'data!'$a$11:$a$10000,5,false)))

Column E is a date, the vlookup in Column O is also returning a date.

 

[Brian from Maui]

In column O I have a vlookup along the lines of this:

=if(iserror(vlookup($a13,'data!'$a$11:$a$10000,5,false)),"",(vlookup($a13,'data!'$a$11:$a$10000,5,false)))

Column E is a date, the vlookup in Column O is also returning a date.

My bad, try,

=SUMPRODUCT(--($E$11:$E$65435>=$C13),--($E$11:$E$65435<=$D13),--(ISNUMBER(O$11:O$65435)))

 

[Humate]

Thanks Brian, that works perfectly