Results 1 to 5 of 5

Thread: Use COUNTA in SUBTOTAL

  1. #1
    Neophyte PierreG's Avatar
    Join Date
    Nov 2015
    Location
    Cape Town, South Africa
    Posts
    3
    Articles
    0

    Use COUNTA in SUBTOTAL



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I have a list of data that is evaluated by either a blank, "Y" or "N" in a column.
    I use COUNTA to calculate the quantity of "Y" in the list.
    I now need to filter the list according to another column called Category, but the COUNTA does not show only the filtered data.

    A B

    1 Category Y/N
    2 Cat B N
    3 Cat A N
    4 Cat A Y
    5 Cat C Y
    6 Cat B
    7
    8 Total Yes: 2 (this amount is calculated through COUNTA, but column A will now be filtered)



    Thanks
    Pierre

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi
    you might need to take a look at the SUBTOTAL function

  3. #3
    Neophyte PierreG's Avatar
    Join Date
    Nov 2015
    Location
    Cape Town, South Africa
    Posts
    3
    Articles
    0
    Hi
    Yes I did, but could not get the SUBTOTAL to only add only the cells containing a "Y" like COUNTIF does.
    SUBTOTAL adds all text cells, regardless of content.

    Is there a way to combine a COUNTIF and a SUBTOTAL formula?
    I tried something like:
    =COUNTIF(SUBTOTAL(3,B2:B20),"Y")
    but it does not work.
    I also tried to begin the formula with SUBTOTAL by it also does not work.



    Thanks

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit
    I also tried to begin the formula with SUBTOTAL by it also does not work.
    show us the formula you used in this case.

  5. #5
    Neophyte PierreG's Avatar
    Join Date
    Nov 2015
    Location
    Cape Town, South Africa
    Posts
    3
    Articles
    0
    To select only the cells marked "Yes", I used:
    =COUNTIF(B6:B17,"Yes")
    This returned a total of 5 because there are 5 cells marked with "Yes". The rest are marked No


    But column A contains a criteria A, B or C
    Criteria A only has one "Yes", meaning if I filter only criteria A then the answer should only be 1.

    I cannot get SUBTOTAL to work that it only shows me the 1

    I hope you understand.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •