VBA or excel function to automate a process

[abhi5a]

<table style="width: 414px; height: 108px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 222pt;" width="296"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl108" style="height: 15pt; width: 222pt;" height="20" width="296">Hi,

I work in project management and would like some excel tips to incorporate into my spreadsheet.

Listed below are the steps I follow in counting the number of tasks that fall under a certain category in a given time period:

Step 1 - Filter on category 'Alpha' in Column A</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl108" style="height: 15pt;" height="20"> Step 2 - Filter on Date range ( 4- 15 July) </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl108" style="height: 15pt;" height="20"> Target - Count # of tasks in scope after step 2

Column A - contains the different categories under which tasks are classified in a certain project.

My Question - How do I count the number of tasks under 'alpha category' between a certain date without doing it manually? i.e. - How do I use VBA or other functions to automate this process?

Thanks
</td> </tr> </tbody></table>

 

[Marcelo Branco]

Hi,

Welcome to the forum!

Assuming the Categories in A1:A100 (adjust to suit) maybe this formula can help

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1:$A$100,ROW($A$1:$A$100)-ROW($A$1),0,1)),--($A$1:$A$100="Alpha"))

put it in an empty cell that will be visible after the filtering process.

HTH

M.