Sumproduct #value! error

Humate

New Member
Joined
Feb 13, 2006
Messages
43
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
 

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
Joined
Feb 16, 2002
Messages
8,459
Try,

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

Humate

New Member
Joined
Feb 13, 2006
Messages
43
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
Joined
Feb 16, 2002
Messages
8,459
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
Joined
Feb 13, 2006
Messages
43
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
Joined
Feb 16, 2002
Messages
8,459
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)))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top