Forum Discussion

Alan Curry's avatar
Alan Curry
Copper Contributor
Sep 12, 2018

Return the sum of formulas that contain a name

I have a spreadsheet where I have named cell N2 "STR" for Sales Tax Rate.  Then I use STR in multiple formulas throughout the spreadsheet.

 

Is there a way to search the spreadsheet for formulas that contain STR and return the sum of those cells?

 

On my spreadsheet, all of the formulas that contain STR are in column W.

Formulas and Functions

6 Replies

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Sep 13, 2018

    pls see if the attached can help.

    it will search for formula in the cells and add those with formula (with equal sign)

    press ALT F8 and run the macro

    thanks..

     

    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      Sep 13, 2018
      if you can give me the formula - I could change the criteria to search for that formula and add them accordingly..
      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor
        Sep 13, 2018

        if what I just understood from your explanation is right, it is much complicated than I thought. That being said, I think the simplest way is to name the cells then sum it.
        to select non-contiguous cells you may use Ctrl + mouse click all the selected cells then name it. say, the cells name is "curry", in the designated cell, enter =sum(curry) - that's it.
        sincerely hope that this helps....thanks

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Sep 13, 2018

    Mr. Curry

    try these sites:

    https://www.techrepublic.com/blog/microsoft-office/identify-which-cells-in-excel-are-formula-cells/

    https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

    https://www.rondebruin.nl/win/s9/win006.htm

     

    HTH

    thanks..

Resources