Forum Discussion
Array Formula Error Help Request
Hi, Can anyone explain why formula #1 works, but formula #2 does not? Formula #1 =SUMIFS(HistGLdata!$AO:$AO,HistGLdata!$C:$C,$B41,HistGLdata!$D:$D,$C41,HistGLdata!$E:$E,$D41)/1000 Formul...
Does column E on HistGLdata contain a #N/A value? SUMIFS will simply ignore it, but the array HistGLdata!$E:$E=$D41 will contain #N/A, so the sum will be #N/A too.
Try
=SUM((HistGLdata!$AO:$AO)*(HistGLdata!$C:$C=$B41)*(HistGLdata!$D:$D=$C41)*(IFERROR(HistGLdata!$E:$E,"")=$D41))/1000
(No need to use -- if you're multiplying)