Sum of filtered cells only

[Mark Anderson]

First post here so hello to everyone.

I'm working on a project which manages a window cleaning round.

I'm quite pleased with it so far - it tells me when jobs are due, if they are late, how much customers owe and other stuff.

To create a job list I use the filters, narrowing the list of work down to the areas I want and the work due.

Here's my question:

I know that if I highlight a column of cells (eg. price per clean) that as I perform various filters the taskbar tells me the total of the cells (total value of job list); But is there a way of doing the same thing by means of a formula without the need to highlight the cells beforehand?

Thank you in advance.

Mark

Hope that makes sense!

[Colin Legg]

Hi Mark,

Yes, you can do this using the SUBTOTAL worksheet function. The formula would be something like:
=SUBTOTAL(9,A2:A100)

where A2:A100 is the entire range you want to sum.

The key is that SUBTOTAL ignores rows hidden by filtering. The 9 argument means that it will SUM the visible cells. The Excel helpfile has some good information on this function.

HTH
Colin

[Mark Anderson]

Fantastic! and really quick response - thanks!