# Sumproduct #value! error

#### Humate

##### New Member
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?

Humate

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Brian from Maui

##### MrExcel MVP
Try,

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

#### Humate

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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.

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

#### Humate

##### New Member
Thanks Brian, that works perfectly

Replies
3
Views
148
Replies
10
Views
165
Replies
18
Views
368
Replies
3
Views
145
Replies
3
Views
267

1,181,730
Messages
5,931,718
Members
436,800
Latest member
abowalid98

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back