+ Reply to Thread
Results 1 to 3 of 3

If then or?

  1. #1
    Registered User
    Join Date
    08-12-2018
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    1

    If then or?

    4400 165

    6.5 NOF

    If Cell A2 is a number I want to divide by itself and multiply by cell A1, then I want to do the same for column 2 and sum the total but if cell B2 is not a number or equal to zero as shown here I'm not able to divide by itself and the formula returns an error. Help?

    The result of this table should be (6.5/6.5*4400)+(NOF/NOF*165) = 4400

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,188

    Re: If then or?

    If you divide a number by itself the result is 1. I'm not sure what you are trying to do.

    Pete

  3. #3
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    628

    Re: If then or?

    Hi,

    If you want to SUM Row 1 when corresponding Row 2 is Not Text or 0 (zero), use E1 formula, if Row 2 zero is to be included, then use E4 formula:

    Excel 2016 (Windows) 64 bit
    A
    B
    C
    D
    E
    1
    4400
    165
    500
    4400
    2
    6.5
    NOF
    0
    3
    4
    4400
    165
    500
    4900
    5
    6.5
    NOF
    0
    Sheet: Sheet30

    Excel 2016 (Windows) 64 bit
    E
    1
    =SUMPRODUCT((ISNUMBER(A2:C2))*(A2:C2<>0)*A1:C1)
    4
    =SUMPRODUCT((ISNUMBER(A5:C5))*A4:C4)
    Sheet: Sheet30

    Change cell references/range to match your data.
    Last edited by jtakw; 08-12-2018 at 06:17 PM.

+ 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