Advanced Subtotal

[craig.jenkins]

Hi All,

I currently have somehting like this in my spreadsheet:

Date Trainer What Course Month of course

7-Jan Sarah Nationwide Jan
8-Jan Sarah Nationwide Jan
9-Jan Sarah Nationwide Jan
10-Jan Sarah RADARS Jan
11-Jan Sarah RADARS Jan
12-Jan Sarah Nationwide Jan
13-Jan Sarah Update Jan
14-Jan Sarah Auth Jan
15-Jan Sarah Nationwide Jan
16-Jan Sarah Nationwide Jan

I want to be bale to count How many course are going on for a praticular month but it needs to work when you use a filter. In the above example I am cuurently using the below formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(D20:D50000, ROW(D20:D50000) - ROW(D20),0,1))*(D20:D50000="RADARS"))

However, this formula only counts the amount of RADARS in the list. I want a formula to show ith the Month you filter, i.e Jan, there is 1 nationwide course, 1 update course, 1 auth course, 1 radr course and so on. Is this possible? It needs to work when you use a filter. Can any one help?

Many thanks

Craig.

[EdMac]

Hi Craig,

Sumproduct can work with multiple criteria, just add additional ones to the end of your formula.

Not sure why you need to use subtotals.

If I was doing it, I would have some cells to enter/select the search criteria from a drop down liste.g. Month, course type and then use these as the criteria in my sumproduct formula. Say Month is selected in A1, course in A2

Please Login or Register  to view this content.

Does that help?

Ed