Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1

HI
Try this =SUMPRODUCT(--(A3:A40="January"),--(C3:C40="Manager1"))
Just change Month and Jobtitle.
Adjust range to your needs.
HTH
John
"Excel-User-RR" wrote in message
...
Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1

Hi John,
Unfortunately your suggestion does not return the result that I am looking
for. I need a formula to ignore duplicate jobtitles in the same session #.
Sorry if my explanation of the problem was convoluted. I want a unique
jobtitle count for each month excluding duplicates. Does that help?
Thanks.
"John" wrote:

HI
Try this =SUMPRODUCT(--(A3:A40="January"),--(C3:C40="Manager1"))
Just change Month and Jobtitle.
Adjust range to your needs.
HTH
John
"Excel-User-RR" wrote in message
...
Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1

For unique count, try:
=SUMPRODUCT((A3:A40="January")*(C3:C40="Manager1")/COUNTIF(C3:C40="Manager1"))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Excel-User-RR" wrote:

Hi John,
Unfortunately your suggestion does not return the result that I am looking
for. I need a formula to ignore duplicate jobtitles in the same session #.
Sorry if my explanation of the problem was convoluted. I want a unique
jobtitle count for each month excluding duplicates. Does that help?
Thanks.
"John" wrote:

HI
Try this =SUMPRODUCT(--(A3:A40="January"),--(C3:C40="Manager1"))
Just change Month and Jobtitle.
Adjust range to your needs.
HTH
John
"Excel-User-RR" wrote in message
...
Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1

Hello Luke, I must apologize again for not being clear. What I am trying to
achieve is a count of each jobtitle, for each month, per each session. So if
the same jobtitle was listed in 2 different sessions in the same month - it
would be counted twice instead of once. Your formula give me a result of 1,
but what I need is a result of 2 for Manager1 in January (1 for session #1
and 1 for session #3). Please let me know if this description is still
unclear. Thanks.
"Luke M" wrote:

For unique count, try:
=SUMPRODUCT((A3:A40="January")*(C3:C40="Manager1")/COUNTIF(C3:C40="Manager1"))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Excel-User-RR" wrote:

Hi John,
Unfortunately your suggestion does not return the result that I am looking
for. I need a formula to ignore duplicate jobtitles in the same session #.
Sorry if my explanation of the problem was convoluted. I want a unique
jobtitle count for each month excluding duplicates. Does that help?
Thanks.
"John" wrote:

HI
Try this =SUMPRODUCT(--(A3:A40="January"),--(C3:C40="Manager1"))
Just change Month and Jobtitle.
Adjust range to your needs.
HTH
John
"Excel-User-RR" wrote in message
...
Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1

I think you'll have to use VBA. Right click on sheet tab, view code, and
paste this in. I've commented out the lines that will require you to change
things.

Sub UniqueCounter()
Dim xCount As Integer
Dim SessionNumber As Integer
Dim Title As String

'This is where you input the range
For Each cell In Range("A3:A40")
If cell.Value = SessionNumber And _
cell.Offset(0, 3).Value = Title Then GoTo skipcell
SessionNumber = cell.Value
Title = cell.Offset(0, 3).Value

'This is where you change month value
If cell.Offset(0, 1).Value = "January" And _
'This is where you change job title
cell.Offset(0, 3).Value = "Manager1" Then
xCount = xCount + 1
End If
skipcell:
Next cell
'Change this to where ever you want the count displayed
Range("E1").Value = xCount
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Excel-User-RR" wrote:

Hello Luke, I must apologize again for not being clear. What I am trying to
achieve is a count of each jobtitle, for each month, per each session. So if
the same jobtitle was listed in 2 different sessions in the same month - it
would be counted twice instead of once. Your formula give me a result of 1,
but what I need is a result of 2 for Manager1 in January (1 for session #1
and 1 for session #3). Please let me know if this description is still
unclear. Thanks.
"Luke M" wrote:

For unique count, try:
=SUMPRODUCT((A3:A40="January")*(C3:C40="Manager1")/COUNTIF(C3:C40="Manager1"))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Excel-User-RR" wrote:

Hi John,
Unfortunately your suggestion does not return the result that I am looking
for. I need a formula to ignore duplicate jobtitles in the same session #.
Sorry if my explanation of the problem was convoluted. I want a unique
jobtitle count for each month excluding duplicates. Does that help?
Thanks.
"John" wrote:

HI
Try this =SUMPRODUCT(--(A3:A40="January"),--(C3:C40="Manager1"))
Just change Month and Jobtitle.
Adjust range to your needs.
HTH
John
"Excel-User-RR" wrote in message
...
Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1

Oops, based my response on John's answer, and I didn't see the bit about
session number duplicates. I'm afraid my formula won't work either.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Excel-User-RR" wrote:

Hi John,
Unfortunately your suggestion does not return the result that I am looking
for. I need a formula to ignore duplicate jobtitles in the same session #.
Sorry if my explanation of the problem was convoluted. I want a unique
jobtitle count for each month excluding duplicates. Does that help?
Thanks.
"John" wrote:

HI
Try this =SUMPRODUCT(--(A3:A40="January"),--(C3:C40="Manager1"))
Just change Month and Jobtitle.
Adjust range to your needs.
HTH
John
"Excel-User-RR" wrote in message
...
Please help with finding a formula to count how many of each jobtitle per
month, by session # listed in a table would be appreciated. Sometimes 2
jobtitles are in the same session. I am looking for results that would be in
a 2nd table which displays count of each jobtitle by month. For example, I
would like to calculate how many of the Manager1 jobtitle were from January
(result=2) and Director1 for February would be 3. I am hung up on the fact
that the session number repeats and I only want to count each jobtitle in
each session once using Excel 2003. Can someone help me please? Thank you
very much.
# Month Interviewer Jobtitle
1 January John Smith Manager1
1 January Mary Jones Manager1
1 January Tom Todd Manager2
1 January Mary Jones Manager2
1 January John Smith Manager2
2 January Sue Allen Supervisor1
2 January Tony Pike Supervisor1
3 January Larry Fox Manager1
3 January Tom Todd Manager1
4 February Sue Allen Director1
4 February Larry Fox Director1
5 February Jane Doe Director1
5 February Tony Pike Director1
5 February Roy Redd Director1
6 February Mary Jones Director1
6 February Tom Todd Director1