- Joined
- Jan 22, 2010
- Messages
- 3
- Reaction score
- 0
I'm tearing my hair out over this formula, so I'd be grateful for any help!
I've got a spreadsheet for a team of minute takers which calculates various figures re whether deadlines are being met for each person.
In one cell, I have the array formula '=SUM(($B$2:$B$99="R CPC")*($E$2:$E$99="CS")*($J$2:$J$99<=28))'. This calculates how many sets of 'R CPC' minutes by 'CS' have been distributed within the 28 day deadline. This is then taken into account in other formulas which calculate monthly/yearly totals and percentages etc. for each specific person. I also have similar formulas which calculate how many have gone past deadline for different types of meetings.
The problem I am having is that until all of the cells have been filled in for a particular month, they are counted as being out of the 28 day deadline, so when my other formulas are adding everything together, the statistics look much worse than they are.
What do I need to add to the above formula so that any blank cells in column J are not taken into account? I *think* this is where the problem lies, but then again I may be wrong!
Thanks in advance.
I've got a spreadsheet for a team of minute takers which calculates various figures re whether deadlines are being met for each person.
In one cell, I have the array formula '=SUM(($B$2:$B$99="R CPC")*($E$2:$E$99="CS")*($J$2:$J$99<=28))'. This calculates how many sets of 'R CPC' minutes by 'CS' have been distributed within the 28 day deadline. This is then taken into account in other formulas which calculate monthly/yearly totals and percentages etc. for each specific person. I also have similar formulas which calculate how many have gone past deadline for different types of meetings.
The problem I am having is that until all of the cells have been filled in for a particular month, they are counted as being out of the 28 day deadline, so when my other formulas are adding everything together, the statistics look much worse than they are.
What do I need to add to the above formula so that any blank cells in column J are not taken into account? I *think* this is where the problem lies, but then again I may be wrong!
Thanks in advance.
Last edited:
