Forum Discussion
Solved
EXCEL FORMULA
Hi, hope Members of 'MICROSOFT TECH COMMUNITY' are well. i have a problem as below PIC-1 ITEM CODE ITEM NAME QTY ITEM CODE ITEM NAME QTY ITEM CODE ITEM NAME QTY ITEM ...
You can, of course, expand the ranges in the formula:
=SUM(IF(('ITEM RECEIVED'!$D$3:$X$100000=B2)*('ITEM RECEIVED'!$E$3:$Y$100000=C2),'ITEM RECEIVED'!$F$3:$Z$100000))
You can then enter data in up to 100000 rows in the ITEM RECEIVED sheet and the formula will still work.
Remark: you must use Ctrl+Shift+Enter when you enter or edit the formula, not before you enter the formula, as you seem to suggest when you write "This is the PICTURE after Using Ctrl+Shift+Enter before applying FORMULA in Cell 'O10'"
Depends on your Excel version / platform, as variant that could be
=SUMPRODUCT( ($B$3:$P$5=$B9)*($C$3:$Q$5=$C9)*IF( ISNUMBER($D$3:$R$5), $D$3:$R$5, 0) )