0

How to get the count of unique keys with two conditions. Ex. Room C3 dated 2/1

Answer : 2 Since 123 and 124 is only the unique keys under C3 and 2/1

Keys Room. Dates
123  C1    2/1
125  C1    2/4
124  C3    2/2
123  C3    2/1
124  C3    2/1
123  C3    2/1
Girl
  • 23
  • 5
  • Look into `COUNTIFS` – cybernetic.nomad Mar 04 '19 at 19:48
  • 2
    did you try googling something like [excel count multiple criteria](https://www.google.com/search?q=excel+count+multiple+criteria&rlz=1C1GCEU_enUS821US821&oq=excel+count+multiple&aqs=chrome.0.0j69i57j0l4.3983j1j7&sourceid=chrome&ie=UTF-8)? Tons of answers that negate the need for this question. – Scott Holtzman Mar 04 '19 at 19:51
  • Sorry @cybernetic.nomad and Scott Holtzmann, but Countifs does not cut it for this. It will not return a unique count. – teylyn Mar 04 '19 at 20:17
  • @teylyn, you need to combine countifs with sumproduct. –  Mar 05 '19 at 00:31
  • @user11138753 I know that. I'm just saying that it can't be done with just Countifs, as the comments seem to imply. – teylyn Mar 05 '19 at 19:27
  • @ScottHoltzman there were no given formula to get the unique values. – Girl Mar 06 '19 at 17:51

1 Answers1

0

With Excel a unique count has always been a challenge. Approaches involve either long, convoluted array formulas (see here or this question ) or using Pivot tables as an intermediate step.

With the new Dynamic Array functions currently in preview for Office 365 Insider builds, this is a piece of cake, though.

This formula entered into just one single cell, no copying, will filter the data to return only the unique rows:

=UNIQUE(FILTER(B4:D9,(C4:C9=G2)*(D4:D9=G3),""))

The screenshot shows that the data automatically "spills" into the rows below.

enter image description here

If you are only interested in the final count, then filter only the first column and count the resulting data in one formula:

=COUNT(UNIQUE(FILTER(B4:B9,(C4:C9=G2)*(D4:D9=G3),"")))

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • As I write in my answer, it requires the Office 365 Insider build which has the new dynamic array formulas. It does not work in other versions of Excel. – teylyn Mar 06 '19 at 20:29