Closed Thread
Results 1 to 5 of 5

COUNTIFS on Visible Cells only

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Northridge, CA
    MS-Off Ver
    Excel 2007
    Posts
    21

    COUNTIFS on Visible Cells only

    Basically, I have a spreadsheet that a variety of countif and countifs functions that I am using to analyze data with. I need these to count only visible cells after I apply the auto filter. Please see below:

    =COUNTIFS(K2:K500, "Y", F2:F500, "Y")

    This is the example I need to sove for and the rest of the functions have different cell range references but if I can get a solution on these two, I will be able to trnslate them to the rest f the worksheet. This is what I have tried:

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(F2,ROW(F2:F500)-ROW(F2),0)),(F2:F500="Y")+0)

    This will work when I am only counting the "Y" in column F. I obviously want it to count the "Y" when it appears in columns K and F on visible cells only. Your assistance is greatly appreciated.

  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: COUNTIFS on Visible Cells only

    Is this the same question?

    http://www.excelforum.com/excel-2007...ells-only.html
    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
    Registered User
    Join Date
    01-12-2012
    Location
    Northridge, CA
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: COUNTIFS on Visible Cells only

    Actually, yes but I figured out a work around to the COUNTIF problem where I was counting the "Y" from the range in E2:E500 from another column I had set up with all the account numbers entered into the tracker so I wound up doing this:

    =SUMPRODUCT(SUBTOTAL(2,OFFSET(C2,ROW(C2:C500)-ROW(C2),0)))

    Where column C is where the user enters the account number so I am able to just count the number of entries basicaly which serves my purpose. I still have a problem making the formula work from this post in the General Forum, specifically the COUNTIFS(K2:K500, "Y", F2:F500). I basically need to translate that formula to work when I filter the data.

  4. #4
    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: COUNTIFS on Visible Cells only

    In any case, be sure, that best way to describe your problem is to upload a sample workbook.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: COUNTIFS on Visible Cells only

    I replied to the original here
    please make all replies in that thread, thanks. This one is closed
    Audere est facere

Closed 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