Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 48
Default Conditional Subtotal with data table

Hi, guys,

I am using data tables in Excel 2010 for Windows.

I have this formula:

=SUBTOTAL(102,Table2[Key])

I want the subtotal to be conditioned on whether the key
value is greater than 16000000 (or more humanly readable,
16,000,000).

Thanks for the ideas.

/s
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Conditional Subtotal with data table

Hi Sarah,

Am Thu, 7 Jan 2016 13:10:06 +0000 (UTC) schrieb Sarah H.:

=SUBTOTAL(102,Table2[Key])

I want the subtotal to be conditioned on whether the key
value is greater than 16000000 (or more humanly readable,
16,000,000).


can't you filter the Key by 16.000.000?
If not try following formula (the values are in column A):
=SUMPRODUCT(SUBTOTAL(2,INDIRECT("A"&ROW(2:1000)))* (A2:A100016000000))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 48
Default Conditional Subtotal with data table

Claus Busch wrote:
Hi Sarah,

Am Thu, 7 Jan 2016 13:10:06 +0000 (UTC) schrieb Sarah H.:

=SUBTOTAL(102,Table2[Key])

I want the subtotal to be conditioned on whether the key
value is greater than 16000000 (or more humanly readable,
16,000,000).


can't you filter the Key by 16.000.000?
If not try following formula (the values are in column A):
=SUMPRODUCT(SUBTOTAL(2,INDIRECT("A"&ROW(2:1000)))* (A2:A100016000000))


That's perfect! Thanks very much!

I can't filter on that dependably because I want to see the data
filtered other ways but still know how many are in that key category.

I understand what each function does that you gave,but I don't
quite understand why the INDIRECT works its magic here to help
my situation.

/s
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Conditional Subtotal with data table

Hi Sarah,

Am Thu, 7 Jan 2016 13:53:28 +0000 (UTC) schrieb Sarah H.:

I understand what each function does that you gave,but I don't
quite understand why the INDIRECT works its magic here to help
my situation.


you need the INDIRECT function that only the visible rows in the range
A2:A1000 are calculated.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 48
Default Conditional Subtotal with data table

Claus Busch wrote:
Hi Sarah,

Am Thu, 7 Jan 2016 13:53:28 +0000 (UTC) schrieb Sarah H.:

I understand what each function does that you gave,but I don't
quite understand why the INDIRECT works its magic here to help
my situation.


you need the INDIRECT function that only the visible rows in the range
A2:A1000 are calculated.


Thank you, Claus! That makes perfect sense now.

/s
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
Conditional SUBTOTAL in XL210 Table Sarah H.[_2_] Excel Discussion (Misc queries) 7 January 9th 15 11:59 AM
Subtotal based on a range of data in Pivot Table HenrikH Excel Worksheet Functions 5 September 25th 09 07:35 AM
Subtotal pivot table data to show % split Sarah (OGI) Excel Discussion (Misc queries) 0 November 5th 08 01:25 PM
Conditional Subtotal in table Esh Excel Discussion (Misc queries) 3 December 20th 07 05:40 PM
Pivot Table: showing data as % of subtotal kg Excel Discussion (Misc queries) 1 August 2nd 07 10:37 PM


All times are GMT +1. The time now is 12:19 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"