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

    Unanswered: Column counts w/Look up cells & #N/A

    I'm trying to put this out there again because I still don't have a working resolution for this.

    As shown in the screenshot, I need to sum these columns around the #N/As.
    The cells all include the Lookup formula to pull other counts from another tab.

    Whatever formula is used, it must be entered into the totals row.

    I've tried a could of of other suggestions and they didn't work.

    TIA!

    MillB
    Attached Thumbnails Attached Thumbnails Counts.JPG  

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I already provided a solution on your other thread here, right?
    http://www.dbforums.com/microsoft-ex...-theres-n.html

    Or was that question different? As I said on that other thread, option 3 is definitely a viable option. If it doesn't work and has been correctly implemented then it means that the underlying data is numbers stored as text rather than numbers. If you're still having trouble then I suggest you zip and attach the workbook...

  3. #3
    Join Date
    Dec 2006
    Posts
    39
    Hi Colin,

    Thanks for your help.
    Yeah, I tried all those options and none of them worked.

    The cells on the Lookup worksheet and in these columns are all formatted to number. I changed the range to above the total cells and I get zero in all.
    ??? See attachement.

    Thanks again.
    Attached Thumbnails Attached Thumbnails 3b.JPG  

  4. #4
    Join Date
    Dec 2006
    Posts
    39
    Like you said, it might be easier to attach the workbook. lol

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hiya,

    The formatting of the cells doesn't change the underlying data types which are what I think are causing the problem: if that SUMIF() formula I posted on the other thread is returning zero then I think the numbers in your table are actually strings. In one column, as a test, try amending the lookup formulas so that there is a 0+ at the start, eg:
    Code:
    =0+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)
    The reason for doing this is that adding a zero to the lookup result will coerce it from text (string data type) to a number (double data type).

    Then, here's that SUMIF() formula again for reference:
    Code:
    =SUMIF(B2:B10,"<=9.99999999999999E307")
    Fingers crossed...
    Last edited by Colin Legg; 12-07-09 at 20:26.

  6. #6
    Join Date
    Dec 2006
    Posts
    39

    It worked!!!

    Colin ~

    Thank you so much for your help!
    Absolutely amazing. I added 0+ at the beginning of the LOOKUP formulas in each cell and add the SUMIF. SHAZAAM!

    Thanks again
    Attached Thumbnails Attached Thumbnails 3c.JPG  

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Great stuff, Mill.

Posting Permissions

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