multiple criteria count formula with duplicate data

[Excel-User-RR]

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

 

[John]

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]

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.

 

[Luke M]

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

 

[Luke M]

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.

 

[Excel-User-RR]

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]

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

 

[Excel-User-RR]

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 A117. Any further assistance you can
offer is welcome if you have time. Thanks.

 

[Luke M]

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

 

[Excel-User-RR]

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]

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).