Conditional functions on portions of cell data

[sabow71]

I manage a strength report for my employer that accounts for each employee
using a unique number assignment system. This system is designed to allow me
to identify which of nine teams an employee belongs to, identifying their
specific assignment within that team and also their employment status (i.e.,
contractor, intern, volunteer).

For example, I am 621-03. The "6" indicates which team I am on. The "2"
identifies my assignment (Quality Assurance). And the "1" indicates
employment status.

My strength report needs to provide an accountability breakdown for teams
and broken down further by employment status. Is there a function that can
extract a specific digit/character from a cell entry?

Currently, I am specifying cell ranges for each group. As the organization
expands, this will present an accuracy problem as valid cells are possibly
excluded from count.

How do I do this?

 

[Francis]

try the Left and Mid functions as :

from your example 621-03
to extract 6, use =LEFT(B2) in C2
to extract 2, use =MID(D18,2,1) in D2
change the above formula's argument from 2 to 3 to extract 1 in E2
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[sabow71]

This works if I am only checking a single cell... I need to run a tally of
entries that fit multiple criteria (e.g., all employees on the same team,
same employment status, and that there is an employee filling the slot
number).

My roster is a tally sheet of all available positions within our
organization. In other words, there are more numbers than employees. So when
I run my tally, I need to count only those positions that are filled, then
sort my count by team and employee status.

On my spreadsheet, Column A has the number assignment. Column D contains the
name of the person filling that position. Column E contains their status for
the day (present, sick, vacation...) The function I am working with is here:

=COUNTIFS(A:A, IF(MID(A:A,1,1)=0,TRUE,FALSE), D, =TRUE, E:E, "Annual")

So the first range and criteria is number assignment and validation for
correct team using an IF/MID combination. The second range/criteria validates
that there is a person filling that position. The last range/criteria checks
for a status of "Annual".

It's not working. What am I doing wrong?

 

[Glenn]

I manage a strength report for my employer that accounts for each employee
using a unique number assignment system. This system is designed to allow me
to identify which of nine teams an employee belongs to, identifying their
specific assignment within that team and also their employment status (i.e.,
contractor, intern, volunteer).

For example, I am 621-03. The "6" indicates which team I am on. The "2"
identifies my assignment (Quality Assurance). And the "1" indicates
employment status.

My strength report needs to provide an accountability breakdown for teams
and broken down further by employment status. Is there a function that can
extract a specific digit/character from a cell entry?

Currently, I am specifying cell ranges for each group. As the organization
expands, this will present an accuracy problem as valid cells are possibly
excluded from count.

How do I do this?

=MID("621-03",2,1)

would give you your assignment and

=MID("621-03",3,1)

would give you your employment status. Is that what you are looking for?

 

[sabow71]

I figured out a portion of my problem... Here is what I have so far:

=COUNTIFS(A:A,"=0?1-??",E:E, "=Annual")

The first range/criteria validates the correct team and employee status
throughout the entire column rather than a specified finite range. The second
range/criteria is for the employee's daily status. In this case, counting
only those who are on annual leave.

Each row of data represents a position in the organization. If a person's
name is in the name field then the position is filled. Otherwise, it is
vacant. I don't care about counting vacancies. The remaining problem: How do
I count only those rows that are filled by a person in addition to the other
criteria already listed?