[AhujaA]

Hi,

Im trying to use sumproduct formula subject to meeting two conditions, but the data values may contain some #VALUE! values. Because of this my final value is giving #VALUE! Error. Any suggestions how to tackle it?

For example:
a 1990 2

a 1991 5

a 1992 3

a 1990 #VALUE!

a 1991 6

a 1992 7

a 1990 8

a 1991 15

a 1992 12



I want to add where column A is “a”, Column B is “1990” and shud get 10 as result for this condition and not the #VALUE! error.

Any suggetions plz?

[alow]

Which version of Excel are you using? If it's 2007 or later, you can use a SUMIFS formula instead, although I don't think that will fix the problem with the #VALUE! errors. Why are you gettings those? I think it would be easier to set up an IF formula in the column with the numbers to hide those with a 0 to make the whole thing simpler.

[alow]

Ha, nevermind the version question, it's in the title. But my recommendation to make things simpler is still to get rid of the #VALUE! errors first.

[AhujaA]

Im using excel 2003 . I cant get rid of #VALUE! cells as these files are coming from some other department.I cant modify the formulas or links to these cells or any other files.

[alow]

All you would need to do is add a column D that has =if(iserror(c2),0,C2) and do the sumproduct on that column instead. If that still doesn't work, I can think about any way to put error trapping in the sum product, but I can almost guarantee a mess of a formula.

[AhujaA]

I know that. but I have given here just an example.. there are lot many other sheets that have this error value at place coming from the very source file. Not possible to update all the linking files for n no. of classes. Is there any other possible way you can think of? thanks.

[alow]

Alright, here's the slop, but it's an array formula. You will have to make sure that when you enter it, you use Ctrl+Shift+Enter and any time you edit it, you hit Ctrl+Shift+Enter:

=SUM((((A1:A100)="a")*((B1:B100)="1990")*(IF(ISERROR(C1:C100),0,C1:C100)))

When you enter this with Ctrl+Shift+Enter, it should put curly brackets ({}) around it. Make sure to the change the ranges accordingly. Also, if your years (1990) are text, leave the quotes. If they are numeric values, take the quotes away from that value. Let me know if that does it.

[AhujaA]

My first condition will be a fixed name, year will be changing from 1993 to 2001 (which i can refer it to the cell), I tried using iserror for the sum range, but it gave me wrong sum of totals, dunno why!

[alow]

Are you using Ctrl+Shift+Enter instead of Enter to put the formula in?

[AhujaA]

Tried this formula :
=SUM(((D6779)="M")*((B6:B779)="1993")*(IF(ISERROR(F6:F779),0,F6:F779)))
WITH CONTROL+SHIFT+ENTER BUT GIVING AN ERROR