Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    39

    Question Unanswered: SUM column totals when there's #N/A

    Hey all,

    Here's my delima. Seems like there should be an easy solution out there, but I've tried a couple of things and can't get this to work.

    What I have is a LOOKUP cell range that is pulling counts of Call Status' into the area.

    =LOOKUP(2,1/(('Call Status'!$A$1:$A$100="Last name, First Name")*('Call Status'!$D$1:$D$100="Left Message")),'Call Status'!$G$1:$G$100)

    All I need to do is SUM the columns, but the #N/As (non-existent) row calculations are keeping this from happening.

    What can I do?

    TIA..
    Attached Thumbnails Attached Thumbnails SUM.JPG  

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    The way I see it, you have 3 main choices.

    1. You can amend your conditional lookup formulas so that they do not return #N/A error values. This is going to create a pretty long formula so possibly not the best option.

    2. You can keep your lookup formulas and then use a helper column. The helper column would contain formulas such as this:
    Code:
    =IF(ISNUMBER(B2),B2,)
    where B2 contains the lookup formula. You would then sum this helper column.

    3. You can create a sum formula which sums the lookup formula results and has the capacity to ignore error values. A formula which does this is:
    Code:
    =SUMIF(B2:B10,"<=9.99999999999999E307")
    where B2:B10 contains your lookup formulas.

    Hope that helps...

  3. #3
    Join Date
    Dec 2006
    Posts
    39
    Thanks for the suggestions, Colin_L.

    In response: #1 - totally agree with ya on that one.
    #2 - this seems like the easier way to work with the data, and will probably be the action I take.
    #3 - Didn't work. It returned a value of zero even if there was number in the range.

    Again - much thanks and appreciation!

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Option 3 should work.

    If it returns 0 then that means you have a problem with the underlying data in the lookup table. It means that the numbers in column G are numbers stored as text, so your LOOKUP() formula is also returning a string that represents a number. The SUMIF() formula is looking for number data types, not string data types, which means that they are ignored.

    So if you go for option 3 then you will have to convert column G from text to numbers.


    Good luck with your project...
    Colin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •