AVERAGEIF - Dates based on Column K and Date in Column H to today

[surkdidat]

Hi

I am trying to work out the average number of days a certain workstream has been open (workdays)

So based on Workstream AAA I want to return 4.5 days (6+3)/2

Column H (Date Received)	Column K (Workstream)	(Column for info only)	(Column for info only 2)
20/06	AAA	(open 6 days) 20 21 22 23 26 27	6
26/06	BBB	(open 2 days) 26 27	2
23/06	AAA	(open 3 days) 23 26 27	3

 

[Fluff]

How about

Fluff.xlsm
	H	I	J	K	L	M
1
2	20/06/2023			AAA	AAA	4.5
3	26/06/2023			BBB	BBB	2
4	23/06/2023			AAA
5
Master

Cell Formulas
Range	Formula
M2:M3	M2	=SUMPRODUCT((NETWORKDAYS(+$H$2:$H$10,TODAY()))*($K$2:$K$10=L2))/COUNTIFS($K$2:$K$10,L2)

 

[surkdidat]

How about

Fluff.xlsm
	H	I	J	K	L	M
1
2	20/06/2023			AAA	AAA	4.5
3	26/06/2023			BBB	BBB	2
4	23/06/2023			AAA
5
Master

Cell Formulas
Range	Formula
M2:M3	M2	=SUMPRODUCT((NETWORKDAYS(+$H$2:$H$10,TODAY()))*($K$2:$K$10=L2))/COUNTIFS($K$2:$K$10,L2)

Thank you, I have tried extending this for the whole column (or doing it as a range to cell 1000, but get #VALUE! - I have just realised (guessed) this is because the sheet it comes from is essentially formulas as the data in imported onto the live tracker from a data sheet held elsewhere........... (ie where I have extended it down to row 1000, some of these columns/rows will still be "blank" at present (due to the formula having no data to import into that cell...... =

IF(TableExtract.csv!I833=0,"",TableExtract.csv!I833)

 

[Fluff]

Ok, how about

=SUMPRODUCT((IFERROR(NETWORKDAYS(+$H$2:$H$10,TODAY()),0))*($K$2:$K$10=L2))/COUNTIFS($K$2:$K$10,L2)

 

[surkdidat]

Thank you, and apologies for not giving the full information out first time!

 

[Fluff]

You're welcome & thanks for the feedback.