conditional constraints on subtotal fn

[gmilton]

I need to count instances of particular cell content (alphanum)within a
filtered data table - Data table is filtered on COL A, Range to be
parsed is in COL B.

COUNTA counts instances within total range, and does change if the
range is filtered. Combining SUBTOTAL and IF statements produce #VALUE
errors, EG:

=IF([range]=[ref],SUBTOTAL(3,[range]),0)
=SUBTOTAL(3,IF([range]=[ref],[range]),0)
=SUBTOTAL(3,COUNTIF([range], [ref]))

Condition sums and nested COUNTIF statements do not work either.

In plain language, the function should: Count the instances where
visible (filtered) cells in a selected range (column) contain a
specified value.

 

[Aladin Akyurek]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1))*(Range=V
alue))

Range is the "total range".

 

[gmilton]

Aladin,

Thanks - not an immediate solution - perhaps I don't understand the use
of of sumproduct to do a count or why the offset function is needed and
how to configure it - i will work on these suggestions to nite - I am
using the range as the entire data array, not just column to be parsed
correct? I may play around with conditional sums as well.

any further guidance would be appreciated.

 

[Aladin Akyurek]

Range is the 1D vertical range for which Value must hold.

 

[gmilton]

Aladin -

I really appreciate your help - if you would take a look at the
following (which returns on #VALUE errors) and let me know what Im
doing wrong - this is a very simplified example - the actual data is
more complex and less clean, making use of a pivot table a considerable
task, which would have to replicated in multiple instances, so figuring
out a function to do the job would be a real time saver...Thanks

ROW1 DATA Filter on Col A, seek count of Col B by store
R2 COL_A COL_B
R3 Store Produce
R4 AP grapes
R5 B plums
R6 B n/r
R7 B plums
R8 BP apples
R9 C oranges
R10 C bananas
R11 C n/r
R12 D oranges
R13 D grapes
R14 D bananas
R15 D apples
R16 D bananas
R17 D apples
R18 D oranges
R19 DP bananas
R20 D plums
R21 E oranges
R22 E grapes
R23 E n/r

VALUE fn
R25: apple =SUMPRODUCT(SUBTOTAL(3,OFFSET($A$4:$A$23,ROW
($B$4:$B$23)-MIN(ROW($B$4:$B$23)),,1)*(B25))

R26 oranges
=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$4:$A$23,ROW($B$4:$B$23)-MIN(ROW($B$4:$B$23)),,1))*(B26))

 

[Aladin Akyurek]

What I gathered from your initial post that you're applying AutoFilter to
your data. And, you'd want to count the occurrences of a value (say, Apples)
in B after the AutoFilter is applied. Then:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$4:$B$23,ROW
($B$4:$B$23)-MIN(ROW($B$4:$B$23)),,1)*($B$4:$B$23="Apples"))

will produce the desired count.