Forum Discussion
EXCEL FORMULA
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'"
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
- Respected Mr. Hans Vogelaar Sir, i am very relieved to hear this YOUR Announcement.
A lot of Thanks to YOU
YOURS Sincerely
tarun@1964 I'm active on this forum almost every day, if you have an unanswered question I will look at it.
- Respected Mr.@Hans Vogelaar Sir, Again many many thanks to YOU for YOUR Advice. i apologize YOU in particular for bothering/annoying YOU repeatedly.
Although I think YOU are one of my well-wisher and TEACHER also regarding this subject. But wish you that I can bother/annoy you in future if I have any
problem again.
YOURS Sincerely
tarun@1964 Respected Mr.HansVogelaar Sir, Again many many thanks to YOU for YOUR Advice. i apologize YOU in particular for bothering/annoying YOU repeatedly.
Although I think YOU are one of my well-wisher and TEACHER also regarding this subject. But wish you that I can bother/annoy you in future if I have any
problem again.
YOURS Sincerely
tarun@1964
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'"
Respected Mr. Hans Vogelaar Sir, i understand that YOU are extermly upset having to give the same answer over and over again on the same subject and i apologize to YOU for this.in YOUR early reply to me on 28-06-2022 "Did you confirm the formula with Ctrl+Shift+Enter? That is essential if you have Excel 2016".
in this regard i would like to inform YOU that of course i have followed your instruction before applying the FORMULA , which given by YOU. now i am sure of one thing that there is some problem in my EXCEL Version.
because to day i have downloaded YOUR attachment and it's very fine. but in ITEM RECEIVED Sheet when i will increase Range of ROW DATA,then i should to rewrite the FORMULA.Respected Sir, don't take offense,i want say one thing that i already know the FORMULA "{=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))}"and i tried it many times and could manage/materialised the matter in any way before posting in 'TECH COMMUNITY' to thinking that there is some thing wrong with my method to fixes it. again one thing in my Workbook which i uploaded today(30-06-2022) there in Column 'D' of Sheet 'AVIL ITEM' above stated FORMULA will be used. please see what happend in attached Workbook.YOURS Sincerely
tarun@1964You didn't even bother to enter the formulas? <sigh>