Hi Friend
Am Having issue with the formula and i really need a help with to resolve it
In Column L2 am trying to count AD-222 unique without having duplicate count, appreciate with help from any one
Thanks
Am Having issue with the formula and i really need a help with to resolve it
In Column L2 am trying to count AD-222 unique without having duplicate count, appreciate with help from any one
Thanks
countif with unique_042917.xlsx | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Manger | Mus | Product | Type | Month | January | AD-222 | |||||||
2 | 01/01/2022 0:00 | Manager 1 | 1 | AD-222 | 0.20 | 0.20 | Manager 1 | 4 | #DIV/0! | |||||
3 | 01/01/2022 6:00 | Manager 1 | 1 | AD-222 | 0.20 | 0.20 | Manager 2 | 2 | 1 | |||||
4 | 01/01/2022 6:00 | Manager 1 | 1 | AD-222 | 0.20 | 0.20 | ||||||||
5 | 01/01/2022 6:00 | Manager 1 | 1 | AD-222 | 0.20 | 0.20 | ||||||||
6 | 01/01/2022 6:00 | Manager 1 | 1 | AD-222 | 0.20 | 0.20 | ||||||||
7 | 01/02/2022 6:00 | Manager 2 | 2 | AD-223 | 0.20 | 0.33 | ||||||||
8 | 01/02/2022 6:00 | Manager 2 | 2 | AD-223 | 0.20 | 0.33 | ||||||||
9 | 01/02/2022 6:00 | Manager 2 | 2 | AD-223 | 0.20 | 0.33 | ||||||||
10 | 04/01/2022 6:00 | Manager 2 | 2 | AD-222 | 0.20 | 0.50 | ||||||||
11 | 04/01/2022 6:00 | Manager 2 | 2 | AD-222 | 0.20 | 0.50 | ||||||||
12 | 02/01/2011 2:48 | Manager 3 | 3 | AD-224 | 0.20 | 0.20 | ||||||||
13 | 02/01/2011 4:32 | Manager 3 | 3 | AD-224 | 0.20 | 0.20 | ||||||||
14 | 02/01/2011 6:16 | Manager 3 | 3 | AD-224 | 0.20 | 0.20 | ||||||||
15 | 02/01/2011 6:16 | Manager 3 | 3 | AD-224 | 0.20 | 0.20 | ||||||||
16 | 01/01/2011 5:33 | Manager 3 | 3 | AD-224 | 0.20 | 0.20 | ||||||||
17 | 04/02/2022 6:00 | Manager 2 | 4 | AD-223 | 0.25 | 0.25 | ||||||||
18 | 04/02/2022 6:00 | Manager 2 | 4 | AD-223 | 0.25 | 0.25 | ||||||||
19 | 04/02/2022 6:00 | Manager 2 | 4 | AD-223 | 0.25 | 0.25 | ||||||||
20 | 04/02/2022 6:00 | Manager 2 | 4 | AD-223 | 0.25 | 0.25 | ||||||||
21 | 05/01/2022 6:00 | Manager 1 | 5 | AD-222 | 0.50 | 0.50 | ||||||||
22 | 05/01/2022 6:00 | Manager 1 | 5 | AD-222 | 0.50 | 0.50 | ||||||||
23 | 05/01/2022 6:00 | Manager 1 | 6 | AD-222 | 1.00 | 1.00 | ||||||||
24 | 05/01/2022 6:00 | Manager 1 | 7 | AD-222 | 1.00 | 1.00 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F24 | F2 | =1/COUNTIFS($C$2:$C$24,C2) |
G2:G24 | G2 | =1/COUNTIFS($C$2:$C$24,C2,$D$2:$D$24,D2) |
K2 | K2 | =SUMPRODUCT((L1=$D$2:$D$24)*1,1/COUNTIFS($C$2:$C$24,$C$2:$C$24)) |
L2 | L2 | =SUMPRODUCT((I2=$B$2:$B$24)*1,($D$2:$D$24=L$1)*1,1/COUNTIFS($C$2:$C$24,$C$2:$C$24,$D$2:$D$24,$D$2:$D$24,A2:A24,I1)) |
K3 | K3 | =SUMPRODUCT((I3=$B$2:$B$24)*1,$F$2:$F$24) |
L3 | L3 | =SUMPRODUCT((I3=$B$2:$B$24)*1,($D$2:$D$24=L$1)*1,$G$2:$G$24) |