Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lettie
 
Posts: n/a
Default 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!!!!
  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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<attarkon.ee
Arvi Laanemets


"Lettie" wrote in message
...
Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
order to differentiate the data???

I desperately need it!!!!



  #3   Report Post  
Lettie
 
Posts: n/a
Default

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<attarkon.ee
Arvi Laanemets


"Lettie" wrote in message
...
Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function in
order to differentiate the data???

I desperately need it!!!!




  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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!!!!

  #5   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

Hi


"Lettie" wrote in message
...
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<attarkon.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<attarkon.ee
Arvi Laanemets


"Lettie" wrote in message
...
Can anyone tell me how I use COUNTIF function *in* a SUBTOTAL function

in
order to differentiate the data???

I desperately need it!!!!








  #6   Report Post  
Lettie
 
Posts: n/a
Default

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!!!!


  #7   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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!!!!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel countif function bkcthecat Excel Worksheet Functions 2 March 13th 05 05:57 PM
countif function etan Excel Worksheet Functions 5 February 7th 05 01:55 AM
How do I nesting subtotal function within average function in Exc Amy Yeh Excel Worksheet Functions 4 January 19th 05 07:23 PM
Subtotal And Countif Sheryl Excel Discussion (Misc queries) 4 December 9th 04 08:18 PM
Excel's Pivot Table & Subtotal function should have a median fie. Mary Excel Worksheet Functions 1 December 3rd 04 04:27 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"