Sumproduct + Excel 2003

Discussion in 'General Software' started by AhujaA, May 14, 2012.

  1. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    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?
     
    Last edited: May 14, 2012
    AhujaA, May 14, 2012
    #1
    1. Advertisements

  2. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    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, May 14, 2012
    #2
    1. Advertisements

  3. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    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.
     
    alow, May 14, 2012
    #3
  4. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    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.
     
    Last edited: May 14, 2012
    AhujaA, May 14, 2012
    #4
  5. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    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.
     
    alow, May 14, 2012
    #5
  6. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    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.
     
    AhujaA, May 14, 2012
    #6
  7. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    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.
     
    alow, May 14, 2012
    #7
  8. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    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! :(
     
    AhujaA, May 14, 2012
    #8
  9. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    Are you using Ctrl+Shift+Enter instead of Enter to put the formula in?
     
    alow, May 14, 2012
    #9
  10. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    Tried this formula :
    =SUM(((D6:D779)="M")*((B6:B779)="1993")*(IF(ISERROR(F6:F779),0,F6:F779)))
    WITH CONTROL+SHIFT+ENTER BUT GIVING AN ERROR
     
    AhujaA, May 14, 2012
    #10
  11. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    Found the error.. i guss was with brackets.. its working .. Thankyouu :)
     
    AhujaA, May 14, 2012
    #11
  12. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    Glad to help :thumb:
     
    alow, May 14, 2012
    #12
  13. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    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.
     
    AhujaA, May 14, 2012
    #13
  14. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    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.
     
    alow, May 14, 2012
    #14
  15. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    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!
     
    AhujaA, May 14, 2012
    #15
  16. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    Does the formula change, just the value stays the same? If so, try hitting F9
     
    alow, May 14, 2012
    #16
  17. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    Formula remains the same and so the values.
     
    AhujaA, May 14, 2012
    #17
  18. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    does the fill handle work for you with normal formulas?
     
    alow, May 14, 2012
    #18
  19. AhujaA

    AhujaA

    Joined:
    May 14, 2012
    Messages:
    21
    Likes Received:
    0
    Location:
    London
    nope.. it doesnt work otherwise
     
    AhujaA, May 14, 2012
    #19
  20. AhujaA

    alow Excel Formulator VIP Member

    Joined:
    Mar 20, 2012
    Messages:
    760
    Likes Received:
    0
    Location:
    US
    Take a look at this and make sure that Fill Series is the option selected right after you use the fill handle.
     
    alow, May 14, 2012
    #20
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MagicMitch

    Excel Sumproduct?

    MagicMitch, Jun 10, 2009, in forum: General Software
    Replies:
    0
    Views:
    575
    MagicMitch
    Jun 10, 2009
  2. LoisS
    Replies:
    0
    Views:
    3,525
    LoisS
    Jul 25, 2009
  3. Natc
    Replies:
    0
    Views:
    558
  4. excel2003

    Excel 2003 - Count / SumProduct Combine?

    excel2003, Mar 17, 2011, in forum: General Software
    Replies:
    1
    Views:
    985
    excel2003
    Mar 23, 2011
  5. SHAB
    Replies:
    3
    Views:
    1,532
Loading...

Share This Page