SUMPRODUCT Question?

**powerade1985** · 03-12-2014, 04:20 AM

Hi everyone,

A big thank you for everyone who contributed to the problem I had with the attached Worksheet last week I couldn’t have done it without you

As my experience is limited in Excel please could someone help with the following problem?

As you will see I have attached a draft version of a staff training Matrix. Towards the bottom of the worksheet I'm counting how many staff members have completed the said course, using this formula to count staff members:

=SUMPRODUCT(--(D6:D16>=TODAY()-1095))

Once 1 or more years have past the cell will go red to indicate the staff member is now out of date. The problem I didn’t realise at the time is that not all staff members will need to complete the said course due to their grade. I still want to use this formula but I also want it to acknowledge cells that contain the wording "N/A" are not to be counted.

I assume this can be done?

**powerade1985** · 03-12-2014, 04:27 AM

Sorry attachment below

**adyteo** · 03-12-2014, 04:51 AM

row 18 has the formula you mention and it shows the people that are OK (the dates are less than the limit you impose). I believe you need to take into account the N/A# for the percentage? yes?
in that case, instead of dividing by cell D18, just divide by the count of cells not blank in the column: e.g cell G19: instead of =SUM(G18/B18) have: sum(G18/counta(G6:G16))

**powerade1985** · 03-12-2014, 05:03 AM

Hi Thanks for the reply,

The red box currently signifies either the training has never been completed (so they need to get on and do it) or the staff member doesn’t need to do the training because of their grade. I still need to distinguish between those who need to do the training and those that don’t and N/A suits the bill.

Thanks again