COUNTIF FOR VISIBLE CELLS , PLEASE HELP

  • By visible cells do you mean your data is filtered? If that is the case use =Subtotal(). If you are wanting to use countif use =CountIf(range, criteria)


    EX:


    Range = A1:A10
    =CountIf(A1:A10,"Test")


    If "Test" were entered three times in that range the formula will return 3.

  • Assume your range is in "A1:A2500"


    Select Cell "A2505", you want to place your subtotal below all of your data.


    In cell A2505 place the formula =Subtotal(3,A1:A2500). Now each time you filter your total will change.


    You can also select your data range and go to Data>Subtotal and that will group and outline your data and place subtotals for each item in your list.


    HTH


    Bruce

  • using formulae, you can COUNT on visible cells using the =SUBTOTAL( function


    to add a criteria you may need to add further IFs in seperate cells, or "resort" to VBA to achieve a COUNTIF simulator....


    At work, I want a SUMIF on visible cells only, but have not been able to achieve this just via a single formulae


    hope this helps, albeit negatively

  • CD,



    Quote


    At work, I want a SUMIF on visible cells only, but have not been able to achieve this just via a single formulae


    Following formula will give You an idea about it and it entered as an array-formula, i e Ctrl+Shift+Enter


    =SUMPRODUCT((A5:A14="AA")*SUBTOTAL(3,OFFSET(A5,ROW(A5:A14)-ROW(A5),))*B5:B14)

  • Re: COUNTIF FOR VISIBLE CELLS , PLEASE HELP


    Quote from XL-Dennis

    CD,


    Following formula will give You an idea about it and it entered as an array-formula, i e Ctrl+Shift+Enter


    =SUMPRODUCT((A5:A14="AA")*SUBTOTAL(3,OFFSET(A5,ROW(A5:A14)-ROW(A5),))*B5:B14)


    Dennis,


    had to revisit this recently and worked through it again - it works a treat, nice one


    thanks
    Chris

  • Re: COUNTIF FOR VISIBLE CELLS , PLEASE HELP


    Quote from Chris Davison

    Dennis,


    had to revisit this recently and worked through it again - it works a treat, nice one


    thanks
    Chris


    This is brilliant! But can anyone talk me through what its actually doing! I just dont get it, and if I dont understasnd, how will I learn???

  • Re: COUNTIF FOR VISIBLE CELLS , PLEASE HELP


    Code
    1. =SUMPRODUCT((A5:A14="AA")*SUBTOTAL(3,OFFSET(A5,ROW(A5:A14)-ROW(A5),))*B5:B14)


    SUBTOTAL provides a sum/count of the visible cells, and so is useful to work on filtered data. But you cannot include other conditions within that function.


    SUBPRODUCT, in its evolved form, supports conditional tests, but it works on arrays, so it is not an obvious candidate top work with SUBTOTAL when using conditional tests on filtered data.


    However, OFFSET can be used to within SUBTOTAL rather than a simple range to force SUBTOTAL to return an array of 1/0 values relating to the visible/non-visible rows rather than just the sum.


    This array result can then be passed to SUMPRODUCT, in conjunction with the other conditional test, to create a resultant array of 1/0 values for all visible rows that meet that condition. This is used to multiple by the value array to get a final sum.


    There is a tutorial at http://www.xldynamic.com/source/xld.SUMPRODUCT.html with a detailed explanation.

    HTH


    Bob