+ Reply to Thread
Results 1 to 15 of 15

Countif with Autofilter

  1. #1
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Countif with Autofilter

    I am trying to figure out how to use =countif with auto filter.

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(A$2,ROW(A$2:A$1000)-ROW(A$2),,1)),--(A$2:A$1000="Right"))

    I have used the above formula before, and now I am getting a #REF error? I changed the A to AC in the formula to accommodate where the data resides in my new spreadsheet.. I am not good with these formulas so I am not sure what the 3, and ,,1 mean.. maybe those need to be changed too?

    In my new spreadsheet.. I am posting the formula in AD1, to count AC2:ac1200, when I filter on Column B

  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: Countif with Autofilter

    Hi

    Take a look to this. It works for me. For you?

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(AC$2,ROW(AC$2:AC$1000)-ROW(A$2),,1)),--(AC$2:AC$1000="Right"))
    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 ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Countif with Autofilter

    Please Login or Register  to view this content.
    EDIT: HEY, late for the party!
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  4. #4
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Countif with Autofilter

    Fotis.. I am still getting the #REF error... I had to add a column, so the formula now resides in AE1 to count Column AD1 to AD1200, when filtering on Column B... here is what it looks like:

    =SUMPRODUCT(SUBTOTAL(3,OFFSET(AD$2,ROW(AD$2:AD$1000)-ROW(A$2),,1)),--(AD$2:AD$1000="Right"))

    as you can see all I changed from yours was AC to AD.. however.. is the part in bold supposed to be a$2 or AD$2?

  5. #5
    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: Countif with Autofilter

    Hi

    This works for me...
    Please Login or Register  to view this content.
    @ ron

    Welcome to the party..!
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Countif with Autofilter

    Ron.. this works.. but only if I am counting what is in Column A.. I am not counting what is Column AD.. with the formula posted in AE1.. I tried to change the formula but I am back to a #REF error..

    =SUMPRODUCT(--(A$2:A$1200="Right"),SUBTOTAL(3,OFFSET($AC$2:$ac$1200,ROW($AC$2:$ac$1200)-ROW($AC$2),0,1)))

  7. #7
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Countif with Autofilter

    Have you changed all the AC to AD? Meaning:
    Please Login or Register  to view this content.
    I can't really understand why Fortis' formula is giving you trouble tho...

  8. #8
    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: Countif with Autofilter

    @ ron

    ...I can't really understand why Fortis' formula is giving you trouble tho...
    Fotis..!

  9. #9
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Countif with Autofilter

    You know, I'm probably not getting you. Are you trying to count AD when filtered only? Or is it there any other filter?

    Are you trying to count the corresponding values on another column where cells in AD=Right. Or just count where AD=Right?

  10. #10
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Countif with Autofilter

    @ Fotis, Sorry mate! That's your name? Have you considered changing it to Fortis?

  11. #11
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Countif with Autofilter

    I don't get it.. here's my Spread Sheet stripped down
    Attached Files Attached Files

  12. #12
    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: Countif with Autofilter

    As today, I admit it .. NO ... But if i decide this, do you want to be my Godfather??

    Yes. This is my Name.

  13. #13
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Countif with Autofilter

    @ Fotis: . I think if you ever want to do it. You'd be better of with someone not as hell broke like me...

    @ ctrapper: the reason you're getting Ref errors is because you got #Ref errors in your range. Look at AD776 onwards. Now to solve that maybe you can add another filter to exclude those. I'll get back to you in a min, if you haven't received help by then.

  14. #14
    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: Countif with Autofilter

    You have problem in Ad776...and so on....

    Take a look to the example...

    Edit: ron, just saw your answer.
    Attached Files Attached Files
    Last edited by Fotis1991; 05-30-2012 at 01:37 PM. Reason: Edit

  15. #15
    Forum Contributor
    Join Date
    01-29-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Countif with Autofilter

    Crap I didn't even see that! I must have cut out a couple of rows that hosed my formula.. thanks for catching that.. let me correct and see if that works!

+ 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