Count unique values in excel based on multiple criteria

Question

I've got a spreadsheet with 3 columns and want a formula to count the unique values in column A based on 3 criteria against B and C.

Below is an example of the data and desired output. E2:H2 is where the formula should go. The data contains blank values in column C. Column B is the result of a vlookup.

   A        B        C        D        E        F        G        H
1  Email    List     Date              1/1/19   1/2/19   1/3/19   1/4/19
2  [email protected]  X        1/1/19            2        1        1        0
3  [email protected]  Y        6/3/19
4  [email protected]  Z        2/2/18
5  [email protected]  X        9/1/19
6  [email protected]  X        5/2/19
7  [email protected]  X        
8  [email protected]  X        4/1/19
9  [email protected]  X        3/2/19
10 [email protected]  X        4/3/19

I want to find the number of unique values in column A where column B == X and column C falls within a particular month (provided by E1:H1).

I'd like to avoid using multiple pivot tables for each date range if possible.

I've tried two formulas which don't work.

{=SUM(IF((B2:B10="X")*(C2:C10>=E$1)*(C2:C10<F$1), 1 / COUNTIFS(B2:B10, "X", C2:C10, ">="&E$1, C2:C10, "<"&F$1, A2:A10, A3:A10)), 0)}

=SUMPRODUCT(((B2:B10="X")*(C2:C10>=E$1)*(C2:C10<F$1)) / (COUNTIFS(A:A, A2:A10, B2:B10, "X", C2:C10, ">="&E$1, C2:C10, "<"&F$1)))

I've seen similar questions in Stack Overflow but none worked for me.

Any help appreciated.

Could you please explain why E3=2? I can't see the logic, because there is only one 1/1/19 but there are 3 dates in the same month. Are you counting dates? months? — Foxfire And Burns And Burns
Could you clarify which one are this conditions? As per my understanding, on E3 you are looking for whatever A value on between 1/1/19 - 1/2/19... isn`t it? — David García Bodego
I've updated the question to clarify a bit. For a given month, I want to count the unique values in column A where column B == "X". E3 = 2 because there are two unique column A values that fall in January. — Baasoti

JvdV JvdV · Accepted Answer · 2019-09-18T11:02:44

You could implement some BOOLEAN logic and check if MONTH and YEAR in C:C are the same as the lookup month and multiply that against your "x" criteria in column B:B:

Formula in E2:

=SUM(--(FREQUENCY(IF((MONTH($C$2:$C$10)=MONTH(E1))*(YEAR($C$2:$C$10)=YEAR(E1))*($B$2:$B$10="X"),MATCH($A$2:$A$10,$A$2:$A$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1)>0))

Note: It's an array formula and needs to be confirmed through CtrlShiftEnter

Drag right...

Count unique values in excel based on multiple criteria

1 Answers