+ Reply to Thread
Results 1 to 5 of 5

Getting the column letter to increase when dragging a =countif() function

  1. #1
    Registered User
    Join Date
    01-28-2013
    Location
    Calgary, AB
    MS-Off Ver
    Excel 2007
    Posts
    4

    Getting the column letter to increase when dragging a =countif() function

    so i have a count if function =COUNTIF(Calendar!AO:AO,"A"). This function is placed on a row with a date and references another column on a different sheet. When i drage the function down to count the next day the AO does not increase to AP. How do i get it to do so?

    Thanks

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Getting the column letter to increase when dragging a =countif() function

    =COUNTIF(INDEX(Calendar!$AO:$BZ,,ROW(A1)), "A")
    Does that work for you? Modify your final column accordingly and you might want to tight up the rows too. i.e if you only plan on having data in the first 500 rows, from AO:BA


    =COUNTIF(INDEX(Calendar!$AO$1:$BA$500,,ROW(A1)), "A")
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Getting the column letter to increase when dragging a =countif() function

    HI Junior_Excel,

    Because there is no row information.
    AO:AO refers to only column (not row) and when you drag this formula down, column AO will remain column AO.
    but if you drag this formula to right, AO will change to AP. Hope you got it



    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  4. #4
    Registered User
    Join Date
    01-28-2013
    Location
    Calgary, AB
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Getting the column letter to increase when dragging a =countif() function

    ChemistB - I was unable to get the formula to work. I think maybe i didn't explain very well.

    dilipandey - When i flipped the table it worked. I put the dates in a horizontal orientation and then when dragging accross (horizontally) the columns where changing.

    However, i've been requested to keep the original orientation with dates running vertically.

    Let me restate my problem as i think i oringally stated it unclearly:

    I have multiple columns on one sheet (the calendar) each with a different date. there are rows associated with these dates each representing a person who is On Shift or Absent for that day. there is a cover page (summary) that totals the number of people who are on shift for that day or absent referencing the calendar. This page has the dates in row format (vertical) verses column format (horizontal).

    my current formula is:

    =countif(calendar!range, "Letter Ref")

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Getting the column letter to increase when dragging a =countif() function

    Maybe an example would help. Here is how my formula is working in an example. What is it that I am not understanding?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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