subtotalif function?

John Brookes · May 20th, 2002, 01:47 PM

Can anyone let me know if it is possible to combine/nest a subtotal and a logical function (or a logical function array) so that i can conditionally subtotal data using the autofilter function rather than using the advanced filter? I know that i can add an additional column and put the the if statement in there but i want to apply this "subtotalif" to several sheets for use by other people and i need to be able to change the "if" parameters quickly without unhiding my if column and changing the statement for up to 3500 rows in multiple sheets. I have asked this question before but i still have not had a reply which does what i want. Am i asking too much?

Chris Davison · May 20th, 2002, 07:15 PM

John,

I had this problem a little while back and resorted in the end to putting my Ifs criteria in the columns to the right and then applying the subtotals to each column respectively, so it altered according to the filters, then referenced these subtotals somewhere else

crude, but nothing else seemed to work

John Brookes · May 21st, 2002, 01:35 AM

Chris,

Thanks for the feedback, i have also tried everything i can think of and the only way that seems to work is the method you describe. It seems weird that sumif is a built in function but subtotalif seems impossible to do in a single function!

Chris Davison · May 21st, 2002, 12:46 PM

John,

yeah

I'll dig out my thread on this in a sec.... there were a couple of replies from our heavyweight MVPs which may be useful

sorry - just trying to navigate this new colour scheme

John Brookes · May 21st, 2002, 01:20 PM

Chris,

Thanks, any help you gan give is gratefully appreciated.

Chris Davison · May 23rd, 2002, 12:43 PM

Sorry John, I had a couple of early nights over here....

here's my earlier posting

http://www.mrexcel.com/board/viewtop...c=2160&forum=2

May 20th, 2002, 07:15 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	John, I had this problem a little while back and resorted in the end to putting my Ifs criteria in the columns to the right and then applying the subtotals to each column respectively, so it altered according to the filters, then referenced these subtotals somewhere else crude, but nothing else seemed to work __________________ :: Pharma Z - Family drugstore ::

May 23rd, 2002, 12:43 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Sorry John, I had a couple of early nights over here.... here's my earlier posting http://www.mrexcel.com/board/viewtop...c=2160&forum=2 __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 20th, 2002, 01:47 PM	#1
John Brookes New Member Join Date: May 2002 Posts: 3	Can anyone let me know if it is possible to combine/nest a subtotal and a logical function (or a logical function array) so that i can conditionally subtotal data using the autofilter function rather than using the advanced filter? I know that i can add an additional column and put the the if statement in there but i want to apply this "subtotalif" to several sheets for use by other people and i need to be able to change the "if" parameters quickly without unhiding my if column and changing the statement for up to 3500 rows in multiple sheets. I have asked this question before but i still have not had a reply which does what i want. Am i asking too much?

May 21st, 2002, 01:35 AM	#3
John Brookes New Member Join Date: May 2002 Posts: 3	Chris, Thanks for the feedback, i have also tried everything i can think of and the only way that seems to work is the method you describe. It seems weird that sumif is a built in function but subtotalif seems impossible to do in a single function!

May 21st, 2002, 12:46 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	John, yeah I'll dig out my thread on this in a sec.... there were a couple of replies from our heavyweight MVPs which may be useful sorry - just trying to navigate this new colour scheme

May 21st, 2002, 01:20 PM	#5
John Brookes New Member Join Date: May 2002 Posts: 3	Chris, Thanks, any help you gan give is gratefully appreciated.