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

​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     Read More

​