Count cells in a range dynamically?

cpaavola

Board Regular
Joined
May 13, 2009
Messages
90
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:
EmployeeTotal SalesJune SalesJuly Sales
3483
74610
15454
4375
9354
16352
17355
8349
1332
11321
12325
18329
193212
62712
10275
13276
14278
202711
22511
5233

<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 SalesJune SalesJuly Sales
Elite4010

<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):
EmployeeTotal SalesJune SalesJuly Sales
193212
62712
202711
22511
74610
8349
18329
14278
13276
4375
17355
12325
10275
15454
9354
3483
5233
16352
1332
11321

<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!
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

buxtongt

Well-known Member
Joined
Oct 5, 2011
Messages
709
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

Board Regular
Joined
May 13, 2009
Messages
90
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

Well-known Member
Joined
Oct 5, 2011
Messages
709
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

New Member
Joined
Sep 15, 2011
Messages
30

ADVERTISEMENT

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

Active Member
Joined
Mar 21, 2012
Messages
337
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

Board Regular
Joined
May 16, 2012
Messages
188

ADVERTISEMENT

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

Board Regular
Joined
May 13, 2009
Messages
90
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

Board Regular
Joined
May 13, 2009
Messages
90
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

Active Member
Joined
Mar 21, 2012
Messages
337
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,798
Messages
5,598,105
Members
414,213
Latest member
suxrule

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top