ignore blank cells that are thew result of a formula

Hi - I have the following formula which is working out the average time between calls for call centre agents:

=SUMPRODUCT(--(MONTH(Data!$A:$A)=Sheet2!$B$2),--(Data!$A:$A<>""),--(Data!$C:$C),--(Data!$D:$D=Sheet2!$B$1))/(SUMPRODUCT(--(MONTH(Data!$A:$A)=Sheet2!$B$2),--(Data!$A:$A<>""),--(Data!$C:$C<>""),--(Data!$D:$D=Sheet2!$B$1)))

in the data
Data column A is the dates
Data Column C is the time between each call
Data column D is the agent name

However in the data column C is a formula to work out the time between calls but I have set it so that it only returns a result if the agent and date are the same as the previous line to stop it calculating time between calls by different agents or the same agent but different days. the formula in column C is

=IF(AND(A1=A2,D1=D2),B2-B1," ")

However the first formula doesn't work unless you remove this from the blank cells. I have set up two data sheets on the attached example one with the formula removed from blank cells in column C and one with it in. and the results show on Sheet 2 - any way I can fix this?

Thanks
J

I would suggest you use AVERAGEIFS as it will be considerably more efficient

Formula:

Please Login or Register  to view this content.

Note: I wouldn't advise using the above, and certainly not the SUMPRODUCT, with entire column references.

your original approach will fail as it is

a) coercing Col C (via double unary) which will fail for non-numerics (incl. null strings)
b) coercing Col A (c/o MONTH) which will fail for header row

you can address both of the above by per below

Formula:

Please Login or Register  to view this content.

but, as noted, don't use the above as it is horrendous in terms of performance.

Hi XLent the averagifs does the trick! Thank you. I am not very good at using the date functions in formulas and the sumproduct one was from a sheet where the data and formulas were on a different workbook to each other.

I must do some work and get to grips properly with the date functions.

I have changed the column refs to use the data only I only added the full column refs to the example for speed!

Thanks again

J