Hello all,
I use the following formule to count "x" in column F based on the month.
=SUMPRODUCT((G$3:G$10>=A3)*(G$3:G$10<=EOMONTH(A3;0))*(F$3:F$10="x"))
Column F is "x" when a file is send retour. Column H shows "x" when the the retour is returned. Both with corresponding dates.
Now i would like to calculate the average day it takes before a retour is returned.
I made this test file. I don't see why the Returned column is counting the returns. It works in my "real" file
I use the following formule to count "x" in column F based on the month.
=SUMPRODUCT((G$3:G$10>=A3)*(G$3:G$10<=EOMONTH(A3;0))*(F$3:F$10="x"))
Column F is "x" when a file is send retour. Column H shows "x" when the the retour is returned. Both with corresponding dates.
Now i would like to calculate the average day it takes before a retour is returned.
I made this test file. I don't see why the Returned column is counting the returns. It works in my "real" file
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | 2022 | ||||||||||
2 | Month | Returned | Average time [days] between A and B | A | B | ||||||
3 | January | 0 | x | 28-sep-22 | x | 30-sep-22 | |||||
4 | February | 0 | x | 30-sep-22 | x | 4-okt-22 | |||||
5 | March | 0 | x | 28-sep-22 | x | 3-okt-22 | |||||
6 | April | 0 | x | 30-sep-22 | x | 30-sep-22 | |||||
7 | May | 0 | x | 4-okt-22 | x | 4-okt-22 | |||||
8 | June | 0 | x | 6-okt-22 | x | 6-okt-22 | |||||
9 | July | 0 | x | 6-okt-22 | x | 14-okt-22 | |||||
10 | August | 0 | x | 1-okt-22 | x | 11-okt-22 | |||||
11 | September | 0 | |||||||||
12 | October | 0 | |||||||||
13 | November | 0 | |||||||||
14 | December | 0 | |||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A3:A14 | A3 | =DATE($C$1,ROWS($1:1),1) |
B3:B14 | B3 | =SUMPRODUCT((G$3:G$10>=A3)*(G$3:G$10<=EOMONTH(A3,0))*(F$3:F$10="x")) |