1

Column A include a list of numbers, some are duplicated

Column B include a list of numbers from 1-12 that represent the months

I need to count the unique numbers in column A where the month in column B = 1

Example:

ColumnA ColumnB
123     1
223     2
312     3
412     1
123     1
312     2
123     3

So here for example 123 show up twice when B=1 and 412 once on the same month so the unique count should be = 2

4
  • Please edit your question a bit by adding an example and your expected results, so that the others can help.
    – Kenneth L
    Aug 7, 2014 at 3:10
  • ColumnA ColumnB 123 1 223 2 312 3 412 1 123 1 312 2 123 3 so here for example 123 show up twice when B=1 and 412 once on the same month so the unique count should be = 2
    – Chuku
    Aug 7, 2014 at 3:15
  • 1
    This thread has an answer superuser.com/questions/189762/…
    – Kenneth L
    Aug 7, 2014 at 3:33
  • unless I'm missing something it doesn't cover the month scenario
    – Chuku
    Aug 7, 2014 at 4:05

1 Answer 1

0

I know you want a solution using countif, but I don't know if it can be done with only countif; So, as an alternative, this VBa does it

Option Explicit
Sub CountDuplicatesPerMonth()

Dim row As Integer
row = 1

Range("C:C").Value = "" 'clear the results


Do While (Range("A" & row).Value <> "")

    Dim val As String
    val = Range("A" & row).Value

    Dim month As Integer
    month = Range("B" & row).Value

    Range("C" & row).Value = 0

    Dim innerRow As Integer
    innerRow = 1

    Do While (Range("A" & innerRow).Value <> "")

        Dim innerVal As String
        innerVal = Range("A" & innerRow).Value

        Dim innerMonth As Integer
        innerMonth = Range("B" & innerRow).Value

        If (innerVal = val And innerMonth = month) Then
            Range("C" & row).Value = Range("C" & row).Value + 1
        End If

        innerRow = innerRow + 1

    Loop

   row = row + 1

Loop

End Sub

Before
enter image description here

After VBa runs

enter image description here

1
  • Without VBA: Put in cell C1 "=COUNTIF(A:A; "=" & B1)". Copy-paste down. Using string concatenating combined with cell references in Excel is very powerful and keeps you away from VBA. let me know if this works for you.
    – agtoever
    Aug 7, 2014 at 9:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .