Hello all,
I got 2 sheets (data(1) and data(2)
Data(1) gathers data from data(2)
It keeps track of how many files are returned.
Now i would like to see unique returns.
Since total returns count the total.
Batch 3 in data(2) has been retoured 2 times but it should count for only 1.
If only one x is in the line it should count 1 and not sum up the x.
Can't manage to get it working since now it shows "1" at unique returns while it should show "2"
I got 2 sheets (data(1) and data(2)
Data(1) gathers data from data(2)
It keeps track of how many files are returned.
Now i would like to see unique returns.
Since total returns count the total.
Batch 3 in data(2) has been retoured 2 times but it should count for only 1.
If only one x is in the line it should count 1 and not sum up the x.
Can't manage to get it working since now it shows "1" at unique returns while it should show "2"
Dashboard_test.xlsm | |||||||
---|---|---|---|---|---|---|---|
B | C | D | E | F | |||
1 | 2022 | ||||||
2 | Month | Retour A | Retour B | Total returns | Unique returns | ||
3 | January | ||||||
4 | February | ||||||
5 | March | ||||||
6 | April | ||||||
7 | May | ||||||
8 | June | ||||||
9 | July | ||||||
10 | August | ||||||
11 | September | ||||||
12 | October | 2 | 1 | 3 | 1 | ||
13 | November | ||||||
14 | December | ||||||
15 | Total | 2 | 1 | ||||
data(1) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C12 | C12 | =SUMPRODUCT(('data(2)'!$B:$B>=B12)*('data(2)'!$B:$B<=EOMONTH(B12,0))*('data(2)'!$C:$C="x")) |
D12 | D12 | =SUMPRODUCT(('data(2)'!$B:$B>=B12)*('data(2)'!$B:$B<=EOMONTH(B12,0))*('data(2)'!$H:$H="x")) |
E12 | E12 | =SUM(C12:D12) |
F12 | F12 | =SUMPRODUCT(('data(2)'!$B:$B>=B12)*('data(2)'!$B:$B<=EOMONTH(B12,0))*('data(2)'!$C:$C="x")*('data(2)'!$H:$H="x")) |
B3:B14 | B3 | =DATE($B$1,ROWS($1:1),1) |
C15:D15 | C15 | =SUM(C3:C14) |
Dashboard_test.xlsm | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | H | ||||
3 | Batch | Retour A | Return A | Retour B | ||||||
4 | 1 | 14-okt-22 | ||||||||
5 | 2 | 4-okt-22 | x | |||||||
6 | 3 | 4-okt-22 | x | x | ||||||
data(2) |