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!

 

[buxtongt]

I don't know how you would make this update as you changed the sorting, but you could add another row/column/sheet that would tell you who has earned the elite status overall, for each month and both. Would that suit your needs?

 

[cpaavola]

Not quite. This document will end up being used by multiple people with little to no Excel knowledge, so I'd like it to tabulate for them automatically. Thoughts?

 

[buxtongt]

I think we can come up with formulas that will do it all automatically, I just didn't want to go down that route if it wouldn't work for you.

 

[Etv5002]

OK what is this "Sigma" number? is a set number where everyone above it is elite, or is the number variable for each employee?

If its a set number you can use a "=Countif("Total Sales Range", "">" Sigma Number")

 

[mathchick]

If the only thing that changes is the month, you could add a drop down for the user to select and then have your formula reference that to determine which column to do. You could then use a (complicated set up of) COUNTIF.

 

[Soggy]

This will count the amount of people with the elite total and a monthly elite value but it will need a way to change based on which month you wish to look at.

I have assumes the total elite of 25 and monthly of 8 wiht total in column B and monthly in column C.

=SUMPRODUCT(($B3:$B11>25)*(C3:C11>8))

The only way I can think of doing this combined with a sort is thorugh a macro.

it depends if that is the route you want to go down.

 

[cpaavola]

OK what is this "Sigma" number? is a set number where everyone above it is elite, or is the number variable for each employee?

If its a set number you can use a "=Countif("Total Sales Range", "">" Sigma Number")

The sigma number is basically the average of each column plus the standard deviation of the column. So, yes, the number varies for every month.

 

[cpaavola]

This will count the amount of people with the elite total and a monthly elite value but it will need a way to change based on which month you wish to look at.

I have assumes the total elite of 25 and monthly of 8 wiht total in column B and monthly in column C.

=SUMPRODUCT(($B3:$B11>25)*(C3:C11>8))

The only way I can think of doing this combined with a sort is thorugh a macro.

it depends if that is the route you want to go down.

Yeah, I'm thinking the macro route is the only way to go. I may have to figure out an alternative.

 

[mathchick]

you wouldn't need a macro, just a helper column or look-up table elsewhere in the document.

Have 3 cells for the user to select: month, elite total, elite monthly total. Then in your helper column put the month you want to be checking so you can directly reference it. Do COUNTIF(total column,=elite total cell ref,monthly helper column,elite monthly total cell ref). Sorry, more of a rough sketch than a specific answer, but I'd be happy to elaborate if desired.