Hi TimC

There is no attachment.

Based on your description, you could use the AGGREGATE or SUBTOTAL function. Both can handle filtered data.

Have a look at this link 

http://www.exceluser.com/excel_help/functions/function-aggregate.htm

Sunny Kow

Hi Tim,

I agree with Sunny. Here are some tutorials on our site for the SUBTOTAL Function and AGGREGATE Function. Note: AGGREGATE was new in Excel 2010 so it won't work in earlier versions of Excel.

Let us know if you get stuck.

Mynda

Hello,

 . . . the problem being I only have Excel 2007 at work. Is there a combination of functions,

that could mimic what AGGREGATE does? Something like the difference between COUNT and

SUBTOTAL?

I'll try uploading my sample spreadsheet again.

Hi TimC,

I see that you are already using a defined name with GET.CELL to check if a cell has formula. You can also use GET.CELL to check the row height, see the example attached. (macros should be enable to allow this function to run, it's an old macro function.

However, the subtotal function is all you need, it will add or count only visible rows. There is also a sumproduct version, using the row height column, where you can add any conditions you want.

Catalin

Thanks Catalin,

I think you're going beyond the bounds of my Excel expertise - this reminds me of my Maths

lecture days. I could always follow the solutions, when the lecturer wrote them up on the

blackboard, but when it came to solving the problems at home, I was often helpless:(

I can't see where you've used GET.CELL to check the row height.

I tried the debugger with the TestFiltered and I got a run-time error 91 on the line

Set rngFilter = ActiveSheet.AutoFilter.Range ?

Hi,

The file you attached already has a defined name with the GET.CELL formula in the Refers To field of that name.

GET.CELL is used in a defined name, then the name is used in the Row Height column.

I thought you are aware of that, because you already had another GET.CELL formula to check if the cell has formulas.

To open the Name Manager, click on the Formulas Tab, then in Defined Names section, click the Name Manager button.

You have to edit the name, to see the formula used.

Catalin

Thanks Catalin,

I'm not sure I follow you. Do I have to define another name with GET.CELL to deal with

the row height property?

What about the error message ?

Praying for the people in Nice.

Hi Tim,

Yes, you have to create a defined name with GET.CELL function to get the row height from a cell. The sample file you uploaded contains a GET.CELL function, to check if a cell has formula, I thought you are aware of that.

The function you mentioned is written by Roy, see this topic for more info: /user-defined-function-to-determine-if-range-is-filtered-not-working

You will see more solutions there.

Catalin

Hi Tim

I am not too sure what you wanted.

By looking at your data, my guess is you want to filter a drug (example Drug A) and expect to see the difference between the dates in column F.

Patient ID     Date        Drug     Interval (in days)
AAA001    29-Sep-15      A         0
AAA001    15-Oct-15      A         16 (15-Oct-15 minus 29-Sep-15)
AAA001    05-Nov-15      A         21 (05-Nov-15 minus 15-Oct-15) 
BBB001    23-Jun-15      A         0
BBB001    08-Jul-15       A         15 (08-Jul-15 minus 23-Jun-15 )
CCC001    15-Aug-15     A         0
DDD001    12-Jun-15      A        0
DDD001    03-Jul-15       A        21 (03-Jul-15 minus12-Jun-15)
DDD001    20-Jul-15       A       17 (20-Jul-15 minus 03-Jul-15)
DDD001    27-Jul-15       A       7 (27-Jul-15 minus 20-Jul-15)
DDD001    28-Aug-15      A      32 (28-Aug-15 minus 27-Jul-15)

Am I correct?

Sunny Kow

Hi Tim

If that is what you want then I would suggest using a Pivot Table.

Refer to my attachment.

Sunny Kow

Well done, Sunny. Nice, elegant solution.

Hi Mynda

Thanks. Glad I am able to help out.

Sunny

SunnyKow said
Hi Tim

If that is what you want then I would suggest using a Pivot Table.

Refer to my attachment.

Sunny Kow  

Hello SunnyKow,

Sorry for not replying sooner - I've been away enjoying the wonderful coastline of South Devon and Cornwall.

Thanks for your help. Just to confirm, then, the only way round it, as far as you see things, is to do the

filtering within a pivot table, and then do the date arithmetic there?

Hi Tim

I believe there are always more than one way to do what you wanted.

PT is only my suggestion as it is easy to use (especially when dealing with data) and rarely require formulas (a plus point for most users)

Is it working for you? Do let us know if it is not and maybe we can come up with some other solutions.