+ Reply to Thread
Results 1 to 8 of 8

Summing Cells Excluding Those Containing a Formula

  1. #1
    Registered User
    Join Date
    04-10-2020
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    3

    Summing Cells Excluding Those Containing a Formula

    I have tried
    Please Login or Register  to view this content.
    but Excel merely treats "not(isformula)" as text to be compared, not as a condition. Can this be done with SUMIF? If not, is there another function?

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,075

    Re: Summing Cells Excluding Those Containing a Formula

    Welcome to the forum.

    That wont work, as you have found. What does the formula in question determine?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Summing Cells Excluding Those Containing a Formula

    Hi,

    For Excel 2007 you'd have to save the workbook as macro-enabled, since then you could employ the following definitions within Name Manager:

    Name: MyRange
    Refers to: $A$1:$A$5

    (Or whatever happens to be the range in question.)

    Name: HasFormula
    Refers to: =GET.CELL(48,OFFSET(INDEX(MyRange,1),N(INDEX(ROW(MyRange)-MIN(ROW(MyRange)),,)),))

    You can then use the following formula within the worksheet:

    =SUMPRODUCT(1-HasFormula,MyRange)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    04-10-2020
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Summing Cells Excluding Those Containing a Formula

    Quote Originally Posted by AliGW View Post
    Welcome to the forum.

    That wont work, as you have found. What does the formula in question determine?
    Thanks for your reply. I have data copied from another sheet, with column totals somewhere below the last data. I am trying to ignore the formula-generated value, and it seemed to me that SUMIF was exactly what I needed. If SUMIF can't handle the ISFORMULA condition, I can easily find a work-around; I was asking primarily to add to my understanding of EXCEL.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11)
    Posts
    52,075

    Re: Summing Cells Excluding Those Containing a Formula

    OK - I am not sure you understood my question, but it seems you have a solution offered above.

  6. #6
    Registered User
    Join Date
    04-10-2020
    Location
    Louisville, Kentucky
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Summing Cells Excluding Those Containing a Formula

    XOR LX: Thanks for your solution; this approach would never have occurred to me. Thanks also for introducing me to the GET.CELL function

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: Summing Cells Excluding Those Containing a Formula

    GET.CELL is not a function but a macro. See http://www.sulprobil.com/Get_it_done.../get_cell.html

    If you use it you must save your file as xlsm

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,553

    Re: Summing Cells Excluding Those Containing a Formula

    Quote Originally Posted by ghborrmann View Post
    XOR LX: Thanks for your solution; this approach would never have occurred to me. Thanks also for introducing me to the GET.CELL function
    You're welcome!

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA - Summing column excluding first cell
    By thisandthat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2017, 07:47 PM
  2. [SOLVED] Formula to count cells that are not empty excluding cells with formulas
    By Imbizile in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2015, 07:08 AM
  3. [SOLVED] Summing row of values greater than Zero, excluding minimum
    By jdstuffel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-18-2013, 11:38 AM
  4. Formula needed for dividing 2 sets of cells and excluding adjacent text cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2013, 06:45 PM
  5. Replies: 10
    Last Post: 08-30-2010, 10:22 AM
  6. Summing part of a string excluding cells
    By soph in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-03-2006, 09:10 PM
  7. summing part of a string, excluding cells
    By soph in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2006, 08:10 PM

Tags for this Thread

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