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
Formula #2
=SUM((HistGLdata!$AO:$AO)*(--(HistGLdata!$C:$C=$B41))*(--(HistGLdata!$D:$D=$C41))*(--(HistGLdata!$E:$E=$D41)))/1000
The output of Formula #2 is #N/A. I am using Excel 2021 and have also used Control-Shift-Enter to confirm the dynamic array functionality is active. Formula #2 works until I include the bolded component of the formula. The result for Formula #2 is #N/A even if the bolded component is the only criteria.
Thanks,
Mike
2 Replies
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)
- Yes, the array did contain #N/A and that was the issue. Your suggestion worked!! Many thanks!!
Cheers,
Mike
