testing period dates ([un/]solved)

[david gabra]

Hi

I have a table that show testing pass / fail and the dates - there are many rows.
Each rows is assigned a number (test scenario) and a test scenario may appear more that once

test scenario date completion
1 01/01/2022
1 01/01/2022
1 15/01/2022
2 01/03/2022
2 24/04/2022
3 24/02/2022
3 24/02/2022
3 24/03/2022
3 24/02/2022
3 21/04/2022

is there anway to add to sumarisze the above - to take the unique value of each test scenario and then basically put the max and min - (i tried and did not work)
i could probably manage the last countif,, maybe - but to take unique value of each test scenario and then basically put the max and min in table -- no idea.
[plase without pivots) - i want real time refresh)

testing range
test scenario min max number of test
1 01/01/2022 15/01/2022 3
2 01/03/2022 24/04/2022 2
3 24/02/2022 21/04/2022 5

thanks

I creatd an excel file is that help with the same data.



Basoaly so I am showing the min and max for each date.

 

[ETAF]

what version of excel do you have
you could use maxifs() and minifs() and countif()

to get all the unique test numbers - UNIQUE()

but depends if you have those functions in your version
UNIQUE - is a 2021 or 365 version , and FILTER
Maxifs and minifs - 2019 version

whne you say LIVE - when you enter a new result you just want it to update
that should be fine with functions - and calc on automatic


=UNIQUE(FILTER(A2:A40,A2:A40<>""))
=MINIFS($B$2:$B$40,$A$2:$A$40,J4)
=MAXIFS($B$2:$B$40,$A$2:$A$40,J4)
=COUNTIF($A$2:$A$40,J4)

 

[david gabra]

i have 365 office

trying

 

[ETAF]

should work then with unique
Table starting in column J