+ Reply to Thread
Results 1 to 5 of 5

Isnumber nested with sumproduct

  1. #1
    Registered User
    Join Date
    11-08-2010
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    32

    Isnumber nested with sumproduct

    Dear Experts,

    I am having some values in column A1:A10 with some cell having - (dash). Correspondingly column B1:B10 is also having some values with - related to - in column. To calculate sumproduct of both the columns leaving dashed cell, i type following array formula -

    =if(isnumber(A1:A10),sumproduct(A1:A10*B1:B10),"-")
    but it is returning #value. If we delete all dashed cell, then it returns only. How to make a round about.

    Regards,

    Dastgir

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Isnumber nested with sumproduct

    If you have also dash in column B you get (also) an error on that.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: Isnumber nested with sumproduct

    Hi

    Can you post a smaple file of what you have and show what you want to achieve.


    Chris
    Click my star if I helped Thanks

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Isnumber nested with sumproduct

    Have you tried SUMPRODUCT like this

    =SUMPRODUCT(A1:A10,B1:B10)

    that will ignore any dashes (effectively treat them like zero)
    Audere est facere

  5. #5
    Registered User
    Join Date
    11-08-2010
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Isnumber nested with sumproduct

    Dear Daddylonglegs,

    You did me discover a new way of using sumproduct. it really working wonders. thanks a lot.

    Regards,

    Dastgir

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1