Count unique values with three conditions

[mtrilce]

Good afternoon


I have a formula for counting the unique values in row B, according to two conditions related to a range of dates in column C.


What I need is to add a third condition, so that I can count the unique values in B, in the range of dates between July 1, 2018 and 30, 2018 and also those that have the condition of "Terminado" in column D.


I have been able to develop the matrix formula with two conditions, but I do not get the third condition ... Could you help me?


= COUNT (1 / FREQUENCY (YES ($ C $ 5: $ C $ 104> C3; YES ($ C $ 5: $ C $ 104 <= D3; MATCH ($ B $ 5: $ B $ 104; $ B $ 5: $ B $ 104; 0))); FILA ($ B $ 5: $ B $ 104) -FILA (DESREF ($ B $ 5: $ B $ 104 ;;; 1) + 1))


Thank you very much

[p45cal]

It looks as if your file was created in Excel 2016/365, so there is some built-in functionality that will allow you to do this.
Take a look here: https://www.extendoffice.com/documen...values.html#a2
Where you should see: "In Excel 2013, a Distinct Count function has been added in the pivot table, you can easily apply this feature."
Below that is a good description of how to set it up.
I've done that in the attached, and added a timeline.
Your answer is in cell G5 as I've left it.

I expanded your table by duplicating its data below it, so as to be sure that there were duplicates.
2018-07-16_132046.jpg

[mtrilce]

Thank you very much Pascal


This is a great solution ... Unfortunately, I need to calculate this with a formula, because the value I want to obtain is part of a larger and more complex format ...


Could you help me?


Thanks in advance


Marcela from Peru

[p45cal]

try array-entering into a cell somewhere:

=SUM(IF(FREQUENCY(IF((IF(($D$5:$D$26="Terminada")*($C$5:$C$26> C3)*($C$5:$C$26<= D3)=1,$B$5:$B$26,""))<>"", MATCH((IF(($D$5:$D$26="Terminada")*($C$5:$C$26> C3)*($C$5:$C$26<= D3)=1,$B$5:$B$26,"")),(IF(($D$5:$D$26="Terminada")*($C$5:$C$26> C3)*($C$5:$C$26<= D3)=1,$B$5:$B$26,"")),0)),ROW(B5:B26)-ROW(B5)+1),1))

This works on the extended data I talked about earlier and ignores blanks in column B (they don't go towards the count).
I'm sure it could be shorter, which I'll leave to others.
A user-defined-function written in VBA might be more friendly.

[mtrilce]

Works perfectly!!!!

Thanks so much P45scal!!!

You are a genius

Marcela