Excel Sumproduct + Excel 2003

[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

 

[AhujaA]

Found the error.. i guss was with brackets.. its working .. Thankyouu

 

[alow]

Glad to help

 

[AhujaA]

one more thing, how can I bring this formula forward in other cells. For example I want the sum for all years from year 1993 to 2012? Since its an array, im unable to drag it. Plz help. Thanks.

 

[alow]

If you need it to always look at columns D and B for your criteria, put a dollar sign in front of the columns so that they always look there ($D6:$D779 and $B6:$B779), then press Ctrl+Shift+Enter and try the fill handle. The fill handle should still function normally with array formulas.

 

[AhujaA]

Okay.. so here is my real solution:

SUM((('[1084 On-Levelled Premium and Claims_DFA aggregation.xls]2012 Rebase Ultimate P+C(DFA)'!$D$6:$D$779)=("M"))*(('[1084 On-Levelled Premium and Claims_DFA aggregation.xls]2012 Rebase Ultimate P+C(DFA)'!$B$6:$B$779)=$B23)*((IF(ISERROR('[1084 On-Levelled Premium and Claims_DFA aggregation.xls]2012 Rebase Ultimate P+C(DFA)'!$G$6:$G$779),0,('[1084 On-Levelled Premium and Claims_DFA aggregation.xls]2012 Rebase Ultimate P+C(DFA)'!$G$6:$G$779)))+(IF(ISERROR('[1084 On-Levelled Premium and Claims_DFA aggregation.xls]2012 Rebase Ultimate P+C(DFA)'!$H$6:$H$779),0,('[1084 On-Levelled Premium and Claims_DFA aggregation.xls]2012 Rebase Ultimate P+C(DFA)'!$H$6:$H$779)))))

I am actually summing up two columns (G&H) if conditions "M" & Year ($B23, in red) match up.. I have different years in cells B24 to B42 (starting from 1993) . I want this array to add as per year values, but its only working for one year and giving the same values rest of the cells .(i.e. value of year 1993 here) .. Help!

 

[alow]

Does the formula change, just the value stays the same? If so, try hitting F9

 

[AhujaA]

Formula remains the same and so the values.

 

[alow]

does the fill handle work for you with normal formulas?

 

[AhujaA]

nope.. it doesnt work otherwise

 

[alow]

Take a look at this and make sure that Fill Series is the option selected right after you use the fill handle.