A new counting conundrum

  • Thread starter Thread starter ^'^BatAttaK^'^
  • Start date Start date
B

^'^BatAttaK^'^

Maybe I just haven't played with SUMPRODUCT enough yet to figure this
out. Take a look at the sample below.

http://www.batattak-records.com/Book1.xls

Beginning in A1 I have the list of Supervisor names. In cells B1:B5 I
want to count the number of employees under that Supervisor. To do so
I need to use a list of names (columns F and G). The hard part (for
me that is) is to count the individual names in column G when there
are multiple instances of that name.

Ideas would be greatly appreciated.

Thanks!
 
You could put this formula in H2 and copy it down.........
=IF(G2=G1,"",G2)
Then you would only have one instance of each employee for each
Supvr........

Vaya con Dios,
Chuck, CABGx3
 
Hi!

Try this in cell B2 and copy down:

=SUMPRODUCT((F$2:F$39=A2)/COUNTIF(G$2:G$39,G$2:G$39))

Biff
 
Hi!

Try this in cell B2 and copy down:

=SUMPRODUCT((F$2:F$39=A2)/COUNTIF(G$2:G$39,G$2:G$39))

Biff
Click to expand...

Hi Biff,

Thanks for the suggestion. As you can see this works very well in a
limited application.

However, (and you just _knew_ a "however" was coming huh?) in the
original sample I provided I did not make allowances for employees
shifting between Supervisors. For example if Joe Smith moves from one
Supervisor's team to another then Joe needs to be counted on both
teams as he was on both for a portion of the month.

Here is how it was applied:
http://www.batattak-records.com/sample.xls

If you take a look at the data tab the column the formula is counting
does not make any differentiation between the Angel on Frantz, Leon,
or Lucas' team. The formula works very well when Angel is
differentiated with Angel E. on one team, Angel F. on another and so
on.

=SUMPRODUCT((data!A$2:A$496=A2)/COUNTIF(data!B$2:B$496,data!B$2:B$496))
 
The formula works very well when Angel is differentiated
with Angel E. on one team, Angel F. on another and so on.
Click to expand...

Then why not do that?

The formula works based on unique employee names.

Biff
 
Then why not do that?

The formula works based on unique employee names.

Biff
Click to expand...

The problem is that if the same employee has been on different teams
at any point they are not being counted. If Angel E. is on more than
one team the formula does not work.
 
Hi!

I downloaded your Sample.xls file and saw what you had.
When I changed the formula to reference column F on the
DATA sheet everything worked just fine and returned the
same results that you have on the NEW sheet under ACTUAL
COUNT.

Angel and Angel are not unique to each other. Angel E and
Angel F are. When you list the employees you should
include their group as that creates a unique difference.

Biff
 
Hi!
Simple inelegant but reliable approach (see my previous message) gives
correct answer to this task, and more complicated tasks:
Andrea.......12
Banestiller...1
Beverli......13
Dawn.........14
Elaine........2
Eligio........2
Frantz.......13
Heather.......3
Karlisle......1
Larry........19
Leon.........17
Lorenzo......19
Lucas.........4
Marva.........1
Miriam........1
The only one requirement - data has to be sorted.
Regards
Vladimir Mindin
PS: Follow Don Guillett's remark: "Pls do NOT post a file here." I apologize
for posting file in my previous message.
Rgrds, VM
 
Hi!
Simple inelegant but reliable approach (see my previous message) gives
correct answer to this task, and more complicated tasks:
Andrea.......12
Banestiller...1
Beverli......13
Dawn.........14
Elaine........2
Eligio........2
Frantz.......13
Heather.......3
Karlisle......1
Larry........19
Leon.........17
Lorenzo......19
Lucas.........4
Marva.........1
Miriam........1
The only one requirement - data has to be sorted.
Regards
Vladimir Mindin
PS: Follow Don Guillett's remark: "Pls do NOT post a file here." I apologize
for posting file in my previous message.
Rgrds, VM
Click to expand...

I apologize for not replying. While your approach certainly works,
and quite well I might add, one of the caveats to this is only meant
as a template. Other users will export data from a separate
application and paste it onto a data tab in the Excel workbook. The
formula must be able to function without any manual corrections or
formatting to the data.
 
Hi!

I downloaded your Sample.xls file and saw what you had.
When I changed the formula to reference column F on the
DATA sheet everything worked just fine and returned the
same results that you have on the NEW sheet under ACTUAL
COUNT.

Angel and Angel are not unique to each other. Angel E and
Angel F are. When you list the employees you should
include their group as that creates a unique difference.

Biff
Click to expand...

Hi Biff,

Yep...when each employee is completely unique (Angel E., Angel F.) the
formula works great. However, if there were any instances of an
employee moving from one Supervisor to another then the formula does
not.

Is there any way to do this so that if Angel F. moves from Leon's team
to Miriam's team that she will be counted on both teams?
 
Thank you for clarification. Last question. There are no "manual
corrections or formatting to the data" in my approach, except sorting. Does
it mean that sorting is excluded too?
 
Back
Top