Hi All,
the data which i wish to analyse is as follows
Staff Name Project Priority
JP A
AA B
RS A
JP A
and so on. there are at least 300 projects.
what i want to do is for each staff member eg. JP, i want to count the number of Priority Types.. eg. "JP" has 2 priority "A" projects. and so on.
i have tried the following which has not helped:
the data which i wish to analyse is as follows
Staff Name Project Priority
JP A
AA B
RS A
JP A
and so on. there are at least 300 projects.
what i want to do is for each staff member eg. JP, i want to count the number of Priority Types.. eg. "JP" has 2 priority "A" projects. and so on.
i have tried the following which has not helped:
- pivot table: it works, but because i need to automate the process by which the sums are calculated, and i am not able to "macro-ize a pivot table"
- i entered the following formula using Ctrl+Shift+Enter: {=sum((A2:A300="JP")*(B2:B300="A")). it came back with the following error: #VALUE!
- Another thing i tried was sumproduct with and without Ctrl+Shift+Enter: =sumproduct(A2:A300="JP", B2:B300="A"). the error that appeared in both cases was: #VALUE!