A
Amy via OfficeKB.com
I have a spreadsheet with the following headers:
Name (text); Title (text); Phase I (date); Phase II (date); Phase III (date)
This spreadsheet tracks the level of training completed by each person.
Dates are filled in each of the cells under Phase I, II & III. The dates
range from 1/1/2004 through today. I need to put together a combined stacked
chart (which isn't the problem), showing cumulative data (which is the
problem). I'm struggling with the formula I should use to get the data that
is entered into this table. My chart should show the current status as of
2006 for each month (Jan-Dec), where we are at with rolling out this training.
For example, there are 3 different job titles we are tracking, and there are
3 phases each person must go through. Everyone is at a different phase, and
the training started in 2004. In my chart for 2006, in January, I want to
show that to today's date, X number of job title A's have completed Phase I,
Phase II and Phase III, and Y number of job title B's have completed Phase I,
Phase II and Phase III, and Z number of job title C's have completed Phase I,
Phase II and Phase III training, but I want the counts to only count the
highest level of training each person has completed. February's data will
include January's data, and March will include Jan & Feb, and so on. How in
the world can I do this? I think I'm stuck on using COUNTIF, but I can't
seem to make it work, and I'm thinking there's a formula I could use, but I
can't figure it out. Is it possible to use IF/AND with SUMPRODUCT? Can
anyone help? I would be willing to send a sample file if it would help, so
you can see what the data looks like. The spreadsheet is quite simple, just
as I described in the beginning of this post.
--
Thank you,
Amy@H-D
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200601/1
Name (text); Title (text); Phase I (date); Phase II (date); Phase III (date)
This spreadsheet tracks the level of training completed by each person.
Dates are filled in each of the cells under Phase I, II & III. The dates
range from 1/1/2004 through today. I need to put together a combined stacked
chart (which isn't the problem), showing cumulative data (which is the
problem). I'm struggling with the formula I should use to get the data that
is entered into this table. My chart should show the current status as of
2006 for each month (Jan-Dec), where we are at with rolling out this training.
For example, there are 3 different job titles we are tracking, and there are
3 phases each person must go through. Everyone is at a different phase, and
the training started in 2004. In my chart for 2006, in January, I want to
show that to today's date, X number of job title A's have completed Phase I,
Phase II and Phase III, and Y number of job title B's have completed Phase I,
Phase II and Phase III, and Z number of job title C's have completed Phase I,
Phase II and Phase III training, but I want the counts to only count the
highest level of training each person has completed. February's data will
include January's data, and March will include Jan & Feb, and so on. How in
the world can I do this? I think I'm stuck on using COUNTIF, but I can't
seem to make it work, and I'm thinking there's a formula I could use, but I
can't figure it out. Is it possible to use IF/AND with SUMPRODUCT? Can
anyone help? I would be willing to send a sample file if it would help, so
you can see what the data looks like. The spreadsheet is quite simple, just
as I described in the beginning of this post.
--
Thank you,
Amy@H-D
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200601/1