Calculating average value with specific conditions

[FM1]

Column A - 'Date Received' - contains date values
Column B - 'Status' - contains 'In Progress', 'Unassigned' and 'Closed' values
Column C - 'Product' - contains 'Product A', 'Product B', 'Product C', 'Product D' values
Cell D1 = Today's Date

I need two formulas, where the calculation will return an average value for time unassigned from the point the entry was received for the following two cases:

1. Total average time unassigned (Column B = "Unassigned) for ALL products in column C
2. Total average time unassigned, broken down by product (Column C = "Product A/B/C/D")

 

[Chameleon64]

It's easy if you use a "helper" column (eg column F) which calculates the difference between today's date and the date received. Then you can use the AVERAGEIFS function as follows:

1. AVERAGEIFS($F:$F,$B:$B,"Unassigned")
2. AVERAGEIFS($F:$F,$B:$B,"Unassigned",$C:$C,"Product A")

 

[FM1]

It's easy if you use a "helper" column (eg column F) which calculates the difference between today's date and the date received. Then you can use the AVERAGEIFS function as follows:

1. AVERAGEIFS($F:$F,$B:$B,"Unassigned")
2. AVERAGEIFS($F:$F,$B:$B,"Unassigned",$C:$C,"Product A")

I did have this in mind but was trying to avoid it since the worksheet is already very data heavy.

 

[Chameleon64]

I did have this in mind but was trying to avoid it since the worksheet is already very data heavy.

Perhaps an array formula then (using CTRL-SHIFT-ENTER when entering the formula):

1. ={SUMPRODUCT($D$1-$A$2:$A$1000,--($B$2:$B$1000="Unassigned"))/COUNTIFS($B$2:$B$1000,"Unassigned")}

2. ={SUMPRODUCT($D$1-$A$2:$A$1000,--($B$2:$B$1000="Unassigned"),--($C$2:$C$1000="Product A"))/COUNTIFS($B$2:$B$1000,"Unassigned",$C$2:$C$1000,"Product A")}