Totals change when filtering

**InnesMcc** · 11-23-2011, 11:23 AM

Hello,

I'm trying to get my 'total ordered to date' to change when I filter by month. My 'total problem' changes if I filter by August for example but the 'total ordered to date' doesn't.

I would imagine it's because the 'totals ordered to date' are on a different worksheet but can anyone advise how I would link this so when I filter by month that both totals change?

File attached

Thanks

Innes

**TMShucks** · 11-23-2011, 12:08 PM

The reason is that you are using Subtotal for the Total Problem: =SUBTOTAL(9,C:C). Subtotal does not include rows hidden by a filter.

However, the Total Ordered is a simple Sum of cells in the Manual sheet. There is nothing to cause this to adjust based on a filter.

Regards

**InnesMcc** · 11-23-2011, 03:36 PM

Hello,

sorry, my explanation of what I was wanting to do maybe isn't clear. The 'total problem changing' is fine. I want it to change when I filter so it only shows me total for Aug, Sept etc

I want to know if I filter by receive date on the data tab can I get the 'total ordered' to change by month totals that are on the manual tab? I haven't grouped all the dates in the month into Aug, Sept etc to match the headings on the manual worksheet, which doesn't help my example, but is there anyway to link the data tab and the manual tab so that when I filter by a month both totals change and particularly that the 'total ordered' matches the manual tab months?

I'd imagine some clever piece of formula would be required in F3 on the data tab but it's beyond my abilities.

Apologies if that's still not clear.

Thanks

Innes

**TMShucks** · 11-23-2011, 05:32 PM

You're using an Autofilter to select rows on the data sheet. That means you can actually select a specific day in a month. There appears to be no direct relationship between the information on the Manual sheet and the Data sheet.

The column headings are text values, not dates formatted as months so it's not easy to do a date comparison ... assuming you selected a whole month. And what if you selected August and September?

Can I suggest that you rethink your data structure and what you need to see.

Regards