Remember Me?

#1
May 17th 17, 08:47 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2017 Posts: 1
Counting months in a filtered list

I need to be able to count months in a filtered list

I know this wont work but it will give you an idea of what I am trying to do.

=MONTH(SUBTOTAL(3,AL12:AL10000)=5)

A1
1 5/1/2017
2 8/15/2017
3 6/15/2017
4 6/15/2017
5 8/15/2017
6 6/15/2017
7 7/30/2017
8 8/15/2017
9 6/15/2017
10 5/2/2017
11 7/30/2017
12 6/15/2017
13 6/15/2017
14 6/15/2017
15 6/15/2017

Desired Output:

After Filter Applied

Jan 12
Feb 22
Mar 33
Apr 45
May 20
Jun 30

 #2     May 17th 17, 08:55 PM posted to microsoft.public.excel.worksheet.functions Counting months in a filtered list Sorry Desired Filtered Output: May 2 Jun 8 Jul 2 Aug 3
#3
May 17th 17, 09:27 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,480
Counting months in a filtered list

Hi Derek,

Am Wed, 17 May 2017 12:47:41 -0700 (PDT) schrieb DerekE:

I need to be able to count months in a filtered list

I know this wont work but it will give you an idea of what I am trying to do.

=MONTH(SUBTOTAL(3,AL12:AL10000)=5)

A1
1 5/1/2017
2 8/15/2017
3 6/15/2017
4 6/15/2017
5 8/15/2017
6 6/15/2017
7 7/30/2017
8 8/15/2017
9 6/15/2017
10 5/2/2017
11 7/30/2017
12 6/15/2017
13 6/15/2017
14 6/15/2017
15 6/15/2017

Desired Output:

After Filter Applied

Jan 12
Feb 22
Mar 33
Apr 45
May 20
Jun 30

try:
=SUMPRODUCT(SUBTOTAL(3,INDIRECT("AL"&ROW(12:10000) ))*(MONTH(AL12:AL10000)=5))

Regards
Claus B.
--
Windows10
Office 2016
 #4     May 17th 17, 09:55 PM posted to microsoft.public.excel.worksheet.functions Counting months in a filtered list On Wednesday, May 17, 2017 at 4:27:44 PM UTC-4, Claus Busch wrote: Hi Derek, Am Wed, 17 May 2017 12:47:41 -0700 (PDT) schrieb DerekE: I need to be able to count months in a filtered list I know this wont work but it will give you an idea of what I am trying to do. =MONTH(SUBTOTAL(3,AL12:AL10000)=5) Please help! A1 1 5/1/2017 2 8/15/2017 3 6/15/2017 4 6/15/2017 5 8/15/2017 6 6/15/2017 7 7/30/2017 8 8/15/2017 9 6/15/2017 10 5/2/2017 11 7/30/2017 12 6/15/2017 13 6/15/2017 14 6/15/2017 15 6/15/2017 Desired Output: After Filter Applied Jan 12 Feb 22 Mar 33 Apr 45 May 20 Jun 30 try: =SUMPRODUCT(SUBTOTAL(3,INDIRECT("AL"&ROW(12:10000) ))*(MONTH(AL12:AL10000)=5)) Regards Claus B. -- Windows10 Office 2016 I get #VALUE as a result
 #5     May 17th 17, 09:59 PM posted to microsoft.public.excel.worksheet.functions Counting months in a filtered list On Wednesday, May 17, 2017 at 4:27:44 PM UTC-4, Claus Busch wrote: Hi Derek, Am Wed, 17 May 2017 12:47:41 -0700 (PDT) schrieb DerekE: I need to be able to count months in a filtered list I know this wont work but it will give you an idea of what I am trying to do. =MONTH(SUBTOTAL(3,AL12:AL10000)=5) Please help! A1 1 5/1/2017 2 8/15/2017 3 6/15/2017 4 6/15/2017 5 8/15/2017 6 6/15/2017 7 7/30/2017 8 8/15/2017 9 6/15/2017 10 5/2/2017 11 7/30/2017 12 6/15/2017 13 6/15/2017 14 6/15/2017 15 6/15/2017 Desired Output: After Filter Applied Jan 12 Feb 22 Mar 33 Apr 45 May 20 Jun 30 try: =SUMPRODUCT(SUBTOTAL(3,INDIRECT("AL"&ROW(12:10000) ))*(MONTH(AL12:AL10000)=5)) Regards Claus B. -- Windows10 Office 2016 That did it Thank you!

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Bannor Excel Discussion (Misc queries) 5 August 3rd 09 05:06 PM jermsalerms Excel Worksheet Functions 4 June 5th 06 10:27 PM SOS Excel Worksheet Functions 4 April 5th 06 05:31 PM kkrebs Excel Discussion (Misc queries) 6 September 22nd 05 02:57 PM plato Excel Programming 2 August 5th 04 12:50 PM

All times are GMT +1. The time now is 01:42 PM.