+ Reply to Thread
Results 1 to 3 of 3

Increment in Range of COUNTIF after adding a Row before Last Row.

  1. #1
    Registered User
    Join Date
    10-01-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Increment in Range of COUNTIF after adding a Row before Last Row.

    Hi Members,

    I am using COUNTIF function, the problem arises that if I insert a row after the last row than COUNTIF function do not include the last row in range.

    In the attached workbook, I have two columns name and attendance the last row in sheet COUNT the number of "yes". If I add another row and put yes than the COUNTIF doesn't include that cell in range.

    I know that there is a option in excel which enable to put any row added in between the range is automatically included in the function range but one has to enable this option. As I have a way much complex sheet than the attached example and it needs to be send to several peoples for their inputs therefore I cannot rely on this option since some people have that option enabled while other dont.

    Is there any fullproof way to ensure that any additional rows would be automatically included in range of COUNTIF.

    Thanks and Regards.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Increment in Range of COUNTIF after adding a Row before Last Row.

    welcome to the forum, dan-e. you can use this:
    =COUNTIF(OFFSET(B2,,,ROW(B7)-ROW(B2)),"yes")

    or convert the range to a Table (not sure if this is what you were referring to). click on A1 & press CTRL + T. Press OK.
    Right-click on any cell -> Table -> Total Row
    change formula in B7 to:
    =COUNTIF([Attendance],"yes")
    type in Total Present in A7
    people can still see this if you send to them

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    10-01-2013
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Increment in Range of COUNTIF after adding a Row before Last Row.

    Thankyou very much it did the trick

+ 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] Trying to get the date (Within COUNTIF Formula) to increment when using auto-fill feature.
    By Carmstrong227 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 02:24 PM
  2. VBA countif formula for increment columns
    By dummy10 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-25-2013, 11:14 AM
  3. [SOLVED] Adding Conditions to change the range of cells used in a countif formula
    By tcusack in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-22-2013, 02:12 PM
  4. Does COUNTIF work if adding non-sequential cells i.e. not a range
    By BelindaJS in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-27-2012, 11:07 AM
  5. Adding =countif to an =sumproduct range (Maybe)
    By jayclinton in forum Excel General
    Replies: 4
    Last Post: 10-19-2011, 08:52 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