Leadership

Office Challenge: How would you count personnel selling below quota?

In this week's Office Challenge, Susan Harkins tests your Excel skills by creating a dynamic counting function. Can you meet the challenge?

Using the sheet shown below, how would you count the number of personnel not meeting their quota? Right now, there is no quota stated, that's up to you. There's more than one way to solve this one and it isn't as difficult as you might think. Right now, the sheet counts the sales per person using the following formula:

=COUNTIF(Sales[Personnel],B1)

Credit will go to the easiest solution and the solution that incorporates the existing components the best.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
RRB
RRB

When I first wrote the formula, everything after the "less than" sign did not show (comment?). It should have read: =IF(SumIf(Sales[Personnel],D2,Sales[Sold]) "

dhays
dhays

This page is totaly unreadable in IE 8, all very dark blue or purple except for the notes at the bottom of the page. In FF 5.0 it is mostly readable, the text between "...meet the challenge" and "=countif..." is black on dark blue or purple and not readable either. When I take the style off in IE, then it becomes readable--white background. Usually there is no problem reading these posts.

RRB
RRB

My solution is similar to jbenton, but I assume the quotas might be different for each salesman/woman. I would have a second table of three columns (D to F). First column (Personnel) with the names, second (Quota) with the assigned quotas for each one, third column with this formula (cell F2): =IF(SumIf(Sales[Personnel],D2,Sales[Sold]))

Vitor Carvalho
Vitor Carvalho

I'd like to contribute to this challenge, but first I've what certainly is a completly silly question to ask: - Is it correct to use a named range called "Sales[Personnel]". Why not simply "Sales" or even "Personnel". Sorry, but I couldn't reproduce that in my Excel 2010. Please, I'd like if somebody could explain me what's wrong in my approach. Thanks.

lshanahan
lshanahan

for a quota of 75: =countif(Sales[Sold],"

Vitor Carvalho
Vitor Carvalho

... ok, I've just remembered the feature and the concept of table in Excel! So, thanks anyway. Bye

jbenton
jbenton

I assume the quota is for the total of each person's sales and not just the no of entries in the table (too easy) The only way I can see is to have a second table (say at D4:E8, called "Summary") of two columns, the first ("Personnel") having each name once only and the second ("Total") for their total sales. In E5 and copied below would be the formula: =SUMIF(Sales[Personnel],D5,Sales[Sold]) Then in the top rows (in a similar layout to the example spreadsheet above), D1 would have the word "Quota", E1 the actual quota, D2 the word "Count" and E2 would have the formula: =COUNTIF(Summary[Total],"

Editor's Picks