Calculate average in (for me) difficult sumproduct formula

[Bandito1]

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

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"))

 

[FormR]

I made this test file. I don't see why the Returned column is counting the returns.

Is it because your formula returning the months in column B is expecting the year to be in C3 and not A1?

I use the following formule

Is there a reason you are not using COUNTIFS() rather that SUMPRODUCT()?

Now i would like to calculate the average day it takes before a retour is returned.

Book1
	A	B	C	D	E	F	G	H	I	J
1			2022
2	Month	Returned	Average time [days] between A and B				A		B	B less A
3	01/01/2022	0				x	28/09/2022	x	30/09/2022	2
4	01/02/2022	0				x	30/09/2022	x	04/10/2022	4
5	01/03/2022	0				x	28/09/2022	x	03/10/2022	5
6	01/04/2022	0				x	30/09/2022	x	30/09/2022	0
7	01/05/2022	0				x	04/10/2022	x	04/10/2022	0
8	01/06/2022	0				x	06/10/2022	x	06/10/2022	0
9	01/07/2022	0				x	06/10/2022	x	14/10/2022	8
10	01/08/2022	0				x	01/10/2022	x	11/10/2022	10
11	01/09/2022	4	2.75
12	01/10/2022	4	4.5
13	01/11/2022	0
14	01/12/2022	0
Sheet1

Cell Formulas
Range	Formula
A3:A14	A3	=DATE($C$1,ROWS($1:1),1)
B3:B14	B3	=COUNTIFS(G:G,">="&A3,G:G,"<="&EOMONTH(A3,0),H:H,"x")
C3:C14	C3	=IFERROR(AVERAGEIFS(J:J,G:G,">="&A3,G:G,"<="&EOMONTH(A3,0),F:F,"x"),"")
J3:J10	J3	=I3-G3

 

[Bandito1]

I use sumproduct because the data is pulled from an external worksheet
With countifs i found myself looking at #NA or something each time i opened the file and the external file wasn't open.

On my way home i thought already the helper column.
But is it possible without this helper column?

The data in my external file is rather big and very slow and not as clear as my example.
Not sure if i add alot of helper columns the data in the external file will be even slower.

 

[FormR]

I use sumproduct because the data is pulled from an external worksheet

That's a good enough reason, and if a requirement, then probably worth mentioning at the outset.

The data in my external file is rather big and very slow

Not sure if i add alot of helper columns the data in the external file will be even slower

Using helper columns will almost certainly help to improve speed and performance rather than hinder it.

 

[Bandito1]

I thought this was the solution but..

The data is in some other worksheet. When this sheet isn't opened the formula will show 0.
When i open the other worksheet the correct data shows.

I had same problem with =COUNTIFS and i use now SUMPRODUCT to keep data when the other worksheet is closed.

Is there some way to do the same with this formula?

=IFERROR(AVERAGEIFS('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$J:$J,'\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$G:$G">="&A3,'\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$G:$G,"<="&EOMONTH(A3,0),'\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$F:$F,"x"),"")

So the data stays up to date even when the other worksheet is closed

 

[FormR]

Hi, you'll need to use SUMPRODUCT() to get a conditional SUM and COUNT and divide them.

You'll want to restrict the number of rows used to a sensible maximum for performance reasons - I've used a 1000 rows here.

This is not tested in the slightest - but if not quite right, it hopefully points you in the right direction.

=IFERROR(SUMPRODUCT('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$J1:$J1000,--('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$G1:$G1000>=A3),--('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$G1:$G1000<=EOMONTH(A3,0)),--('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$F1:$F1000="x"))/SUMPRODUCT(--('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$G1:$G1000>=A3),--('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$G1:$G1000<=EOMONTH(A3,0)),--('\\nltilnetapsnas1\data\[Tracker.xlsm]Tracker1'!$F1:$F1000="x")),"")