How to count unique names appeared on specific date for daily report?

[specky_]

Hello everyone,

Urgent help needed to process some data and I have 2 problems right here. I am required to use formula on the end result table, and not filter function.





Problem 1:

How to show that each day, only a certain number of unique name appears.
Eg: On 3/12/2018, Andrea, Thomas and Nicky appeared, so 3 (as in 3 persons) shows in I6



Problem 2

How to show that on each day, the number of person hit with either 4 flags, 3 flags, 2 flags or 1 flag.
Eg: On 5/12/2018, both Andrea and Nicky hit with 3 flags so 2 (as in 2 person) should appear in M8
Note: A person won't hit same type of Flag on the same date


The set of data and end result table attached.


Any assistance is highly appreciated.
Thank you.

[Pecoflyer]

Hi and welcome
is this some kind of homework?

[specky_]

Originally Posted by Pecoflyer

Hi and welcome
is this some kind of homework?

It's a simulation data for a project I'm involving with.

[ArviLaanemets]

Does this to work?

[alpha]

See attachment.
All formulas in J4:J11 and M4:P11 must be confirmed by Ctrl-Shift-Enter !

[p45cal]

Late in the day - in the attached one column added to the source table: in G4 a formula:

=SUMPRODUCT(--($A$4:$A$19=$A4),--($B$4:$B$19=$B4),($C$4:$C$19="Y")+($E$4:$E$19="Y")+($F$4:$F$19="Y")+($D$4:$D$19="Y"))

copied down.
Then 2 pivot tables (using the data model to be able to use Distinct Count)