How do I use COUNTIF in a SUBTOTAL function to differentiate the .

Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
order to differentiate the data???

I desperately need it!!!!

Hi

You can't - there is no conditional aggregate function included into
subtotal.

Or you write an UDF yourself, which does what you want, or you add an
additional column with formula returning True/False depending on your
condition, and set autofilter for this column to TRUE - then ordinary
SUBTOTAL returns what you want.


--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


"Lettie" <[email protected]> wrote in message
news:[email protected]...
> Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
> order to differentiate the data???
>
> I desperately need it!!!!

Thanks for that. Unfortunately if I have a true/ false column the subtotal
doesn't return what I want because it's not including the hidden rows (those
that are false).

What is a UDF and can they be written fairly easily, coz I really need
something that will help.

ta
Lettie

"Arvi Laanemets" wrote:

> Hi
>
> You can't - there is no conditional aggregate function included into
> subtotal.
>
> Or you write an UDF yourself, which does what you want, or you add an
> additional column with formula returning True/False depending on your
> condition, and set autofilter for this column to TRUE - then ordinary
> SUBTOTAL returns what you want.
>
>
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>
> "Lettie" <[email protected]> wrote in message
> news:[email protected]...
> > Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
> > order to differentiate the data???
> >
> > I desperately need it!!!!
>
>
>

You can invoke the Longre Subtotal idiom to effect the CountIf
functionality...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)),--(Range=TRUE))

would count TRUE's in Range.

Lettie wrote:
> Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
> order to differentiate the data???
>
> I desperately need it!!!!

Hi


"Lettie" <[email protected]> wrote in message
news:[email protected]...
> Thanks for that. Unfortunately if I have a true/ false column the subtotal
> doesn't return what I want because it's not including the hidden rows
(those
> that are false).

???

Let's try with an example
You have a table p.e. Date, Name, Value (further you have to interpret them
as range references)
You have a cell with formula
=SUBTOTAL(9,Value)
You set Autofilter to Name = "Jim" - in your table only rows with "Jim" in
Name column are displayed. The formula with SUBTOTAL displays the sum of
Value with Name="Jim";
You set an additional autofilter filter condition, p.e. Date=21.03.2005 -
only rows with "Jim" as Name for this particular date are displayed, and
SUBTOTAL returns the sum of Value for only those rows;
When you have more columns in your table, yo can continue in same way -
setting autofilter conditions for as much columns as you want.
Now, when you don't have values, you want to use to set the filter on,
directly in your table (they are in some another table, or they must be
calculated from existing values, then you need additional column(s) - you
get missing values there with formulas (preferable, as you can set different
autofilter values based on returned values), or you get the condition check
result (TRUE or FALSE).
P.e. with table above, you want to display and sum Values for Jim in January
2005.
a) You add a column Month with formula
=TEXT(Date,"mmmm yyyy")
or
=TEXT(Date,"yyyy.mm")
or whatever format you prefer. Then you remove autofilter and set it on
anew - so the new column is included. Now you set autofilter to "Jim" for
Name column and to "January 2005" for Month column.
b) or you add a column p.e. Condition, and enter the formula
=IF(AND(YEAR(Date)=2005,MONTH(Date)=1),TRUE,FALSE
, again reset the autofilter, an then set the filter to "Jim" for Name and
TRUE for Condition.


>
> What is a UDF and can they be written fairly easily, coz I really need
> something that will help.

UDF is an user defined function. To write one, you have to invoke VBA
editor, insert a workbook module, when you don't have one there, and write a
function like this simple one

Public Function GetUser(AnyTime As Date) As String
GetUser = Application.UserName
End Function

You can call such UDF as any built-in function, but from this workbook only.
To be it available in all workbooks, you have to write it into Personal
Macro Workbook's module, or create an Add-in.

When writing an UDF, you have to consider, that:
1. The function always returns the value, stored in variable with same name
as function itself. I.e. when in my example the function name was GetUser,
then there must be a variable GetUser to which the returned value must be
stored.
2. A function mustn't change anything in workbook. You can't use functions
to write some value into some cell, or change cell formatting, or even move
the cursor to another location. (There are exceptions, but this is too
advanced stuff - for start it's better to take is as a rule) This counts for
code inside function too - when you try to change p.e. active sheet in code
temporarily, then the function doesn't work.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets



>
> ta
> Lettie
>
> "Arvi Laanemets" wrote:
>
> > Hi
> >
> > You can't - there is no conditional aggregate function included into
> > subtotal.
> >
> > Or you write an UDF yourself, which does what you want, or you add an
> > additional column with formula returning True/False depending on your
> > condition, and set autofilter for this column to TRUE - then ordinary
> > SUBTOTAL returns what you want.
> >
> >
> > --
> > When sending mail, use address arvil<at>tarkon.ee
> > Arvi Laanemets
> >
> >
> > "Lettie" <[email protected]> wrote in message
> > news:[email protected]...
> > > Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function
in
> > > order to differentiate the data???
> > >
> > > I desperately need it!!!!
> >
> >
> >

Okay, cool. So, which Range and Row do I use for this? the same one I'm
trying to differentiate?


"Aladin Akyurek" wrote:

> You can invoke the Longre Subtotal idiom to effect the CountIf
> functionality...
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)),--(Range=TRUE))
>
> would count TRUE's in Range.
>
> Lettie wrote:
> > Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
> > order to differentiate the data???
> >
> > I desperately need it!!!!
>

The range you want to run a count. Lets suppose that the area you apply
AutoFilter is A4:F100 which also includes the labels. Lets suppose that
you filter say on column B and you want to count TRUE's in column D.
D5:D100 would be the range you feed to the formula.

Lettie wrote:
> Okay, cool. So, which Range and Row do I use for this? the same one I'm
> trying to differentiate?
>
>
> "Aladin Akyurek" wrote:
>
>
>>You can invoke the Longre Subtotal idiom to effect the CountIf
>>functionality...
>>
>>=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),,1)),--(Range=TRUE))
>>
>>would count TRUE's in Range.
>>
>>Lettie wrote:
>>
>>>Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
>>>order to differentiate the data???
>>>
>>>I desperately need it!!!!
>>