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

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

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

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

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