+ Reply to Thread
Results 1 to 7 of 7

how to replace in formula value with a value that is in particular cell

  1. #1
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    how to replace in formula value with a value that is in particular cell

    Hello,
    I have very simple question. Lets imagine that I have the following formula:

    =COUNTIF('sheet1'!$G$3:G6000,"good")


    I would like to change that formula so it counts not "good" but the value which is in cell A1. So if in cell A1 it is written "good" to look for "good". If I replace content of cell A1 with "bad" to count "bad".
    Thanks a lot!!!

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to replace in formula value with a value that is in particular cell

    Do you mean?

    =COUNTIF('sheet1'!$G$3:G6000,a1)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: how to replace in formula value with a value that is in particular cell

    Like this?
    =COUNTIF(Sheet1!$G$3:G6000,A1)
    Teach me Excel VBA

  4. #4
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: how to replace in formula value with a value that is in particular cell

    How the formula should look like if I have

    =COUNTIF('sheet1'!$G$3:G6000,"<>good")

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: how to replace in formula value with a value that is in particular cell



    =COUNTIF(Sheet1!$G$3:G6000,"<>"&A1)

  6. #6
    Forum Contributor
    Join Date
    01-20-2011
    Location
    liverpool
    MS-Off Ver
    Excel 2016
    Posts
    196

    Re: how to replace in formula value with a value that is in particular cell

    And one last question:
    if by mistake in the file we have space (" ") after the value "good " I I can transform the formula so the space is ignored?

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: how to replace in formula value with a value that is in particular cell

    use trim function for remove space.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ 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] Auto-replace a cell's formula with its value
    By justinx in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2015, 06:24 AM
  2. Search and Replace Text with a formula and adjacent cell with a formula
    By 1p3261 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2015, 12:14 PM
  3. Formula to Replace Portion of Cell that Contains Matched Text to Cell
    By celestealexandra in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2014, 03:56 PM
  4. [SOLVED] Looking to replace contents of a cell with a formula
    By s4driver in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2013, 02:47 PM
  5. How to replace the formula on a cell with the value only with no delimiters
    By jfvillalobospe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-19-2013, 03:52 PM
  6. How to replace a cell with a formula?
    By wjohn13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2013, 06:41 PM
  7. Replies: 3
    Last Post: 10-09-2011, 04:24 PM

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