Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
I'm sorry but I get a syntax error when running this code. It highlights the
section: If cell.Offset(0, 1).Value = "January" And _ 'This is where you change job title I am not very familiar with VBA, so I am not sure if I have done something incorrectly. I really appreciate your efforts to help with this. Per my original example, my data is in cells A1:D17. Any further assistance you can offer is welcome if you have time. Thanks. "Luke M" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
My apologies, I put the comment line in the wrong place (in the middle of a
function, oops!) Sub UniqueCounter() Dim xCount As Integer Dim SessionNumber As Integer Dim Title As String 'This is where you input the range, (one column only!) For Each cell In Range("A1:A17") 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 and job title If cell.Offset(0, 1).Value = "January" And _ 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: I'm sorry but I get a syntax error when running this code. It highlights the section: If cell.Offset(0, 1).Value = "January" And _ 'This is where you change job title I am not very familiar with VBA, so I am not sure if I have done something incorrectly. I really appreciate your efforts to help with this. Per my original example, my data is in cells A1:D17. Any further assistance you can offer is welcome if you have time. Thanks. "Luke M" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
Now I know I am in over my head as I am getting a Run-time error '13': Type
mismatch message after the line: SessionNumber = cell.Value Don't know if this is a good time to quit or if you would like to take one more shot at it. Again, I am grateful for your efforts. "Luke M" wrote: My apologies, I put the comment line in the wrong place (in the middle of a function, oops!) Sub UniqueCounter() Dim xCount As Integer Dim SessionNumber As Integer Dim Title As String 'This is where you input the range, (one column only!) For Each cell In Range("A1:A17") 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 and job title If cell.Offset(0, 1).Value = "January" And _ 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: I'm sorry but I get a syntax error when running this code. It highlights the section: If cell.Offset(0, 1).Value = "January" And _ 'This is where you change job title I am not very familiar with VBA, so I am not sure if I have done something incorrectly. I really appreciate your efforts to help with this. Per my original example, my data is in cells A1:D17. Any further assistance you can offer is welcome if you have time. Thanks. "Luke M" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple criteria count formula with duplicate data
Hmm. Are your session numbers entered as text, or numbers?
Won't be quite as "secure/stable" but you could try deleting all the "Dim" call-out lines. (They define variables as certain types). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Excel-User-RR" wrote: Now I know I am in over my head as I am getting a Run-time error '13': Type mismatch message after the line: SessionNumber = cell.Value Don't know if this is a good time to quit or if you would like to take one more shot at it. Again, I am grateful for your efforts. "Luke M" wrote: My apologies, I put the comment line in the wrong place (in the middle of a function, oops!) Sub UniqueCounter() Dim xCount As Integer Dim SessionNumber As Integer Dim Title As String 'This is where you input the range, (one column only!) For Each cell In Range("A1:A17") 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 and job title If cell.Offset(0, 1).Value = "January" And _ 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: I'm sorry but I get a syntax error when running this code. It highlights the section: If cell.Offset(0, 1).Value = "January" And _ 'This is where you change job title I am not very familiar with VBA, so I am not sure if I have done something incorrectly. I really appreciate your efforts to help with this. Per my original example, my data is in cells A1:D17. Any further assistance you can offer is welcome if you have time. Thanks. "Luke M" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To count the data using multiple criteria in multiple columns | New Users to Excel | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Sum single or duplicate rows with multiple criteria | Excel Worksheet Functions | |||
Identify Duplicate Items Based On Multiple Criteria | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |