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'"
Respected HansVogelaar Sir,
Even though YOU are wasting YOUR precious time for doing me, but in this case too YOUR advice did not work as i wanted. i am very sorry that i could not use YOUR advice. i have use (Ctrl+Shift+Enter) before using formula.
| Sr. No | Item Code | Item Name | |||||||||||||
| AB1 | ABCD | {=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} | |||||||||||||
| AB2 | KLOS | {=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} | |||||||||||||
| AS2 | EFGH | ||||||||||||||
| AS5 | MNOP | ||||||||||||||
| AK3 | VXYZ | {=SUM(IF('ITEM LIST'!$B$3&$C$3=('ITEM RECEIVED'!$D$3:$X$5)&('ITEM RECEIVED'!$E$3:$Y$5),'ITEM RECEIVED'!$F$3:$Z$5))} | |||||||||||||
| AS1 | SKDH | {=SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))} | |||||||||||||
| {=SUMME(WENN(('ITEM LIST'!$B$3='ITEM RECEIVED'!$D$3:$X$5)*('ITEM LIST'!$C$3='ITEM RECEIVED'!$E$3:$Y$5),'ITEM RECEIVED'!$F$3:4Z$5))} | |||||||||||||||
| {=SUMME(WENN(IF('ITEM LIST'!B3&C3=('ITEM RECEIVED'!$D$3:$X$5)&('ITEM RECEIVED'!$E$3:$Y$5),'ITEM RECEIVED'!$F$3:$Z$5)))} | |||||||||||||||
| {=SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))} | |||||||||||||||
Hope YOUR kind assitance wil help me
YOURS Sincerely
tarun@1964
Reaspected HansVogelaar
i have VBA Code as below given by YOU
Sub SAVE_DATA()
Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim m As Long
Dim c As Long
Dim t As Long
Application.ScreenUpdating = False
' Source sheet
Set ws = Worksheets("ITEM RECEIVED")
' Find last used row
m = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
' Copy new items to ITEM LIST sheet
Set wt = Worksheets("ITEM LIST")
' Find last used row in column B on ITEM LIST Sheet
t = wt.Cells(wt.Rows.Count, 2).End(xlUp).Row
' Loop through columns D, H, L, P, T and X on ITEM RECEIVED sheet
For c = 1 To 6
' Loop through the rows
For s = 3 To m
' Item Code filled in?
If ws.Cells(s, 4 * c).Value <> "" Then
' Is it a new one?
If wt.Range("B2:B" & t).Find(What:=ws.Cells(s, 4 * c).Value, LookAt:=xlWhole) Is Nothing Then
' Copy Item Code and Item name to new row
t = t + 1
wt.Cells(t, 2).Resize(1, 2).Value = ws.Cells(s, 4 * c).Resize(1, 2).Value
End If
End If
Next s
Next c
' Copy transactions to SUPPLIER LIST sheet
Set wt = Worksheets("SUPPLIER LIST")
' Find last used row in column C on SUPPLIER LIST Sheet
t = wt.Cells(wt.Rows.Count, 3).End(xlUp).Row
' Loop through rows on ITEM RECEIVED sheet
For s = 3 To m
' New invoice number?
If wt.Range("C2:C" & t).Find(What:=ws.Cells(s, 2).Value, LookAt:=xlWhole) Is Nothing Then
' Copy data
t = t + 1
' Date, invoice no, supplier name
wt.Cells(t, 2).Resize(1, 3).Value = ws.Cells(s, 1).Resize(1, 3).Value
' Item name and quantity
For c = 1 To 6
wt.Cells(t, 2 * c + 3).Resize(1, 2).Value = ws.Cells(s, 4 * c + 1).Resize(1, 2).Value
Next c
wt.Cells(t, 17).Value = ws.Cells(s, 32).Value
End If
Next s
Application.ScreenUpdating = True
End Sub
- 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>
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 @Hans Vogelaar Sir,
in reply to YOUR previous enquiry i used the FORMULA in cell 'D3' of 'ITEM LIST' Sheet in my Workbook as described in attached sample Workbook submitted by YOU on 28-06-2022 as PIC 1, but it shows no neumerical result as PIC 2.
PIC 1 Sheet2 ( Sample Workbook)
Sr. No Item Code Item Name Item Qty AB1 ABCD 22 AB2 KLOS 58 AS2 EFGH 68 AS5 MNOP 43 AK3 VXYZ 37 AS1 SKDH 33 after using formula in my work book
PIC 2 ITEM LIST Sheet (my Workbook)
Sr. No Item Code Item Name AB1 ABCD {=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} AB2 KLOS {=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} AS2 EFGH AS5 MNOP AK3 VXYZ AS1 SKDH Hope YOUR Kind Assist will rest assured me
YOURS Sincerely
tarun@1964
Respected @Hans Vogelaar Sir,
of course Sir, and the picture as below after using Formula as per attachment
Sr. No Item Code Item Name AB1 ABCD {=SUM(IF(('Item Received'!$D$3:$X$5=B3)*('Item Received'!$E$3:$Y$5=C3),'Item Received'!$F$3:$Z$5))} AB2 KLOS {=SUM(IF(('Item Received'!$D$3:$X$5=B4)*('Item Received'!$E$3:$Y$5=C4),'Item Received'!$F$3:$Z$5))} AS2 EFGH AS5 MNOP AK3 VXYZ AS1 SKDH {=SUM(IF(('ITEM RECEIVED'!$D$3:$X$5=B3)*('ITEM RECEIVED'!$E$3:$Y$5=C3),'ITEM RECEIVED'!$F$3:$Z$5))} YOURS Sincerely
tarun@1964
Did you look at the sample workbook attached to my previous reply?