IF/AND used with SUMPRODUCT?

[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

 

[Bob Phillips]

Assuming that they have to do Phase I before they can do Phase II, and
assuming that data is in A:E, I would keep it simple and add another column.
In F2, I would add

=3-COUNTBLANK(C2:E2)

and copy down. This would keep a count of status as it were. The SP formula
for Job Title A and who has done upto Phase II is then simply

=SUMPRODUCT(--(B2:B20="Job Title A"),--(F2:F20=2))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Amy via OfficeKB.com]

That was perfect - Thank you so much!

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