Sumif error

I have wrote the following formula which keeps giving me a #N/A error. I was wondering if any1 can point out where i am going wrong.

Please Login or Register  to view this content.

$C$4:$C$686 = agent names
$B702 = humpty dumpty (which exists in C17 from range $C$4:$C$686)

$E$4:$IE$4 = agent status
$AG$2 = Training (which exists in AP4 from range $E$4:$IE$4)

the answer should be 08:45 rather than #N/A.

Thanks in advance.

In the array formula All your ranges need to be the same length 4:686 but your middle term is E:IE so this is not calcuable, the length of the column just being one cell

Regards

Dav

Thanks Dav, I will try and change $E$4:$IE$4 to $E$4:$IE$686 and see if that works.

Dav, i have tried that and now it gives me a #VALUE! error.

Please Login or Register  to view this content.

Any ideas?

well your ranges highlighted in red

Please Login or Register  to view this content.

why not attach a version of the spreadsheet, or a scaled down version of the spreadsheet, say 20 rows, so we can have a clearer idea of the results

Regards

Dav

are not the same width

Dav, I have attached a sample workbook.

Thanks once again.

thanks, andy i will give that a go.

Or perhaps

=SUMPRODUCT((Pivot!$C$5:$C$31=$B3)*(OFFSET(Pivot!$C$5,0,MATCH($G$2,Pivot!$D$4:$F$4,0),27,1)))

Regards

Dav