0

I am trying the count the number of unique sensors (column 1) that are present by visit duration (column 2). Here is a small portion of the data:

Sensor ID   Implant duration
13113   1
13113   1
13113   1
13144   1
13144   1
13144   2
13144   2
13144   2
13144   2
13144   2
14018   1
12184   2
13052   1
13052   1
12155   2
12155   3
12155   3
13069   2
13069   2
13018   1
13018   1
13019   1
13019   1
13049   1
13054   3
13060   3
13108   2
13108   2

So the count for:
Visit 1 should be 6 (13113, 14018, 13052, 13018, 13019, 13049),
Visit 2 should be 5 (13144, 12184, 12155, 13069, 13108), and
Visit 3 should be 3 (12155, 13054, 13060).

I tried DCOUNTA but it doesn't return the count for the first occurrence, just the total number of entries with an implant duration of 1, 2, or 3. So for example it returns 13 for Visit 1, 11 for Visit 2, and 4 for Visit 3.

I have a lot of data that needs to be preserved and counted so I don't want to apply a filter or remove duplicates.

BillyBoy
  • 111
  • 7
  • I don't want to count duplicate occurrences. So each sensor should be counted only once per `Visit`, no matter how many duplicates are present. Since sensor 12155 shows up twice, the count should record the first time it shows up and exclude any other occurrences – BillyBoy Feb 06 '15 at 18:17

3 Answers3

2

I can do it in a step-wise way .. not sure if this helps:

I put your data in columns A and B. In Column C:

=CONCATENATE(A2,"#",B2)

Column D:

=IF(C2=C1,"",A2)

Column E:

=IF(C2=C1,"",B2)

Column F:

=IF(D2="","",COUNTIF(D:D,D2))

Column G:

=IF(E2="","",COUNTIF(E:E,E2))

At that point, you have the data "flagged" as you need it .. just need to extract it.

If I understand the issue, the results should be:

Visit 1: 7 (13113,13144,14018,13052,13018,13019,13049)

Visit 2: 5 (13144,12184,12155,13069,13108)

Visit 3: 3 (12155,13054,13060)

Ditto
  • 3,256
  • 1
  • 14
  • 28
1

There would appear to be 7 unique Sensors in your sample data (13113, 13144, 14018, 13052, 13018, 13019, 13049) for Visit ID=1, not 6.

=SUMPRODUCT((B2:B29=1)/(COUNTIFS(B2:B29, 1, A2:A29, A2:A29&"")+(B2:B29<>1)))
=SUMPRODUCT((B2:B29=2)/(COUNTIFS(B2:B29, 2, A2:A29, A2:A29&"")+(B2:B29<>2)))
=SUMPRODUCT((B2:B29=3)/(COUNTIFS(B2:B29, 3, A2:A29, A2:A29&"")+(B2:B29<>3)))

It would probably be best to put the Visit ID into a cell and reference the cell in all three places.

  • Sorry for the late response but could you explain this for me @Jeeped? – BillyBoy Feb 27 '15 at 20:19
  • @BillyBoy - Have a look at [Count Unique with SUMPRODUCT() Breakdown](http://answers.microsoft.com/en-us/office/wiki/office_2003-excel/count-unique-with-sumproduct-breakdown/1bc18979-4172-4537-993e-3897f74d6a5b) first. If you need to walk through it, make a sample on 5-10 rows of data and step through using Formulas ► Formula Auditing ► Evaluate formula. If you have some specific unanswered concerns after all that, I'll try to clear up any specific point. –  Feb 27 '15 at 20:49
1

You might use a PivotTable with Sensor ID for ROWS and VALUES (Count of) and Implant Duration for COLUMNS then apply =COUNT() on the columns. Shows which sensor (in order), which duration and the instances of the combinations:

SO28372045 example

pnuts
  • 58,317
  • 11
  • 87
  • 139