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

=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.