CODEWITHSUNDEEP
excelexcel-formula
hdub
hdub

Reputation: 321

Unique values in excel with multiple columns

I'm trying to count unique values from one worksheet to another in excel and am having a bit of trouble. The way it currently works is it counts ALL instances of an event (multiple entries of the event for the same date), when they should be counted as one.

Worksheet #1 looks a bit like this:

DateCalled   Time      Type        LoggedBy       ID        EventDate       EventTime
10/2/2012   10:00AM   Expected       Tom         mjones     10/3/2012     11:00 - 14:00
10/2/2012   10:00AM   Expected       Tom         mjones     10/3/2012     14:00 - 17:00
  .          .         .            .            .            .               .
  .          .         .            .            .            .               .

Worksheet #2 looks a bit like this:

  IDname        ID       TotalCount  Type1-Count   Type2-Count   Type3-Count
Mike Jones     mjones        9            1            7             1

I need the formula on worksheet #2 to count the unique number of entries (e.g., more than one entry per day per ID) and add them to each Type-Count. Currently the formula in each Type-Count column is this:

=COUNTIFS(Worksheet1!$E:$E,$B14,Worksheet1!$C:$C,"Type2")

Where column E is ID on Worksheet1, B14 is the individual's ID on Worksheet2, and Column C is the Type on Worksheet1.

I've tried a few nested IF/COUNTIF statements in SUM but I can't really come up with something that works.

Upvotes: 3

Views: 9551

Answers (1)

barry houdini
barry houdini

Reputation: 46331

This "array formula" should give you the number of different dates for each name/type2 combination

=COUNT(1/FREQUENCY(IF(Worksheet1!$C$2:$C$100="Type2",IF(Worksheet1!$E$2:$E$100=$B14,Worksheet1!$A$2:$A$100)),Worksheet1!$A$2:$A$100))

confirmed with CTRL+SHIFT+ENTER

Note: best to restrict the ranges if you can to make the formula quicker

Upvotes: 1

Related Questions