+ Reply to Thread
Results 1 to 4 of 4

Numbers in formulas keep changing

  1. #1
    Registered User
    Join Date
    04-19-2016
    Location
    Nairobi
    MS-Off Ver
    Excel for Mac 2011
    Posts
    7

    Numbers in formulas keep changing

    Hi. I have set up a spreadsheet with a lot of formulas in it. I find that sometimes on opening the spreadsheet, the figures in some of the formulas have changed. For example:
    =COUNTIF(Pre_implementation!$P$2:$P$200,"Apple")+COUNTIF(Implementation!$P$2:$P$200,"Apple")+COUNTIF(Completed!$P$2:$P$200,"Apple") becomes
    =COUNTIF(Pre_implementation!$P$3:$P$201,"Apple")+COUNTIF(Implementation!$P$4:$P$202,"Apple")+COUNTIF(Completed!$P$2:$P$200,"Apple")

    Is this to do with me deleting lines from the pages the formula is counting from? And how can I set it up so that the figures don't change (ie. I always want it to count from rows 2 to 200). Thanks alot.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,249

    Re: Numbers in formulas keep changing

    maybe

    =COUNTIF(INDIRECT("'"&A1&"Pre_implementation!$P$2:$P$200"),"Apple")+COUNTIF(INDIRECT("'"&A1&"Pre_implementation!$P$2:$P$200"),"Apple")+COUNTIF(INDIRECT("'"&A1&"Pre_implementation!$P$2:$P$200"),"Apple")

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: Numbers in formulas keep changing

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Numbers in formulas keep changing

    Try this...

    =COUNTIF(INDIRECT("Pre_implementation!P2:P200"),"Apple")+COUNTIF(INDIRECT("Implementation!P2:P200"),"Apple")+COUNTIF(INDIRECT("Completed!P2:P200"),"Apple")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Changing Huge Numbers of Hyperlinks (formulas)
    By Nuno W in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2014, 11:22 AM
  2. Replies: 2
    Last Post: 05-15-2013, 11:08 PM
  3. Replies: 5
    Last Post: 11-03-2012, 05:10 PM
  4. [SOLVED] Formula for changing numbers into written numbers
    By southsindygirl in forum Excel General
    Replies: 2
    Last Post: 10-22-2012, 01:41 PM
  5. Replies: 6
    Last Post: 10-08-2011, 09:27 AM
  6. changing numbers and symbols in a cell to be all numbers
    By veronicadx in forum Excel General
    Replies: 7
    Last Post: 04-28-2011, 12:07 PM
  7. Changing a row of positive numbers to negative numbers
    By Wendyobw in forum Excel General
    Replies: 5
    Last Post: 10-23-2009, 02:56 PM
  8. [SOLVED] Changing column of numbers made of formulas to just numbers
    By CJ in forum Excel General
    Replies: 2
    Last Post: 06-14-2006, 09:15 AM

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