Count cells in a range dynamically?

[cpaavola]

Hopefully someone out there can help me out. It's a bit awkward to explain, but I'll do my best!

Below is a sample of 20 employees sorted by their total sales:

Employee	Total Sales	June Sales	July Sales
3	48	3
7	46	10
15	45	4
4	37	5
9	35	4
16	35	2
17	35	5
8	34	9
1	33	2
11	32	1
12	32	5
18	32	9
19	32	12
6	27	12
10	27	5
13	27	6
14	27	8
20	27	11
2	25	11
5	23	3

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Each column has numbers highlighted in green to show who is over an "elite" number, as defined by one of our sigma equations:

	Total Sales	June Sales	July Sales
Elite	40	10

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

When I sort the same grid by "June Sales" it shows me which number of elite "Total Sales" employees are also elite "June Sales" employees. In this case, only one of them (employee 7):

Employee	Total Sales	June Sales	July Sales
19	32	12
6	27	12
20	27	11
2	25	11
7	46	10
8	34	9
18	32	9
14	27	8
13	27	6
4	37	5
17	35	5
12	32	5
10	27	5
15	45	4
9	35	4
3	48	3
5	23	3
16	35	2
1	33	2
11	32	1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I have two requests:

1. I would like to have a formula that counts the number of employees elite in "Total Sales" who are also elite in a particular month's sales.
2. I would like this formula to be able to dynamic, so if I sort by subsequent months (July, for instance) it would give me the same result.

I hope this all makes sense!

 

[Soggy]

The macro shouldn't be too coplicated just wanted to make sure you were ok wth that option before I had a look at it.

When you select the month would you like it to only show thats months column as well?

 

[Soggy]

actually if you have the columns for each month anyway you can just add the sum to the top of each column with:

=SUMPRODUCT(($B3:$B14>AVERAGE($B$3:$B$14)+STDEV($B$3:$B$14))*(E3:E14>AVERAGE(E3:E14)+STDEV(E3:E14)))

This is where your data starts in row 2 and the total column is in B.