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'"
I want to thank all of YOU Three Respected Mr. Hans Vogelaar Sir, Mr. Sergei Balkan Sir, and Mr. Quadruple_ Pawn Sir, It’s amazing assist for me, I know, time is of the essence for YOU, I mean YOU have to spend a lot of YOUR Valuable time to solve many Problems. But nevertheless all of YOUR Efforts and YOUR Precious time have failed in this matter due to my worthless approach. In my earlier POST posted on 25-06-2022 I have furnished as PIC – 1 and PIC - 2. but that will be as Sheet – 1 and Sheet – 2 instead of PIC – 1 and PIC - 2. For this reason, I am especially Sorry to all of YOU. Even after all this events if all of YOU decided to solve my PROBLEM than I will be forever INDEBTED to all of YOU.
PIC of Sheet1
ITEM CODE | ITEM NAME | QTY | ITEM CODE | ITEM NAME | QTY | ITEM CODE | ITEM NAME | QTY | ITEM CODE | ITEM NAME | QTY | ITEM CODE | ITEM NAME | QTY | ITEM CODE | ITEM NAME | QTY |
AB1 | ABCD | 5 | AS2 | EFGH | 14 | AK3 | VXYZ | 14 | AS1 | SKDH | 19 | AB2 | KLOS | 17 | AS2 | EFGH | 42 |
AB2 | KLOS | 18 | AS5 | MNOP | 26 | AS2 | EFGH | 37 | AS1 | SKDH | 14 | AS2 | EFGH | 15 | AB1 | ABCD | 44 |
AS2 | EFGH | 17 | AB1 | ABCD | 17 | AK3 | VXYZ | 23 | AS5 | MNOP | 17 | AB2 | KLOS | 23 | |||
As per above stated PIC-1.it is found that some ITEM CODE, ITEM NAME with their QTY are sorted as per ROW Wise. I want an excel formula (only EXCEL FOEMULA, as because DATA of Column A and Column B in Sheet 2 already populated from Sheet1 by using VBA Code, Courtesy of Respected Mr. Hans Vogelaar Sir). For only Item QTY in Column C of Sheet2 from Sheet1 as stated below PIC-2. as per new DATA insert in PIC-1 of Sheet1 in to next rows same ITEM CODE & ITEM NAME will not be Populate in Sheet2, if found 'ITEM CODE ' and 'ITEM NAME ' are same, except new ITEM CODE & ITEM NAME, But the QTY will continue to be added as per ITEM CODE & ITEM NAME Wise.
PIC of Sheet2
Item Code | Item Name | Item Qty |
AB1 | ABCD | |
AB2 | KLOS | |
AS2 | EFGH | |
AS5 | MNOP | |
AK3 | VXYZ | |
AS1 | SKDH | |
YOURs Sincerely
Tarun@1964
In C2 of Sheet2:
=SUM(IF((Sheet1!$A$2:$M$4=A2)*(Sheet1!$B$2:$N$4=B2),Sheet1!$C$2:$O$4))
Adjust the ranges if the data on Sheet1 extend below row 4.
If you don't have Microsoft 365 or Office 2021, confirm with Ctrl+Shift+Enter.
Then fill down.
Respected Hans Vogelaar Sir, Thanking YOU for help. I have used your code but result shows #VALUE!
My EXCEL Version is 2016
DATE
INVOICE NO
SUPPLIER NAME
ITEM CODE
ITEM NAME
QTY
UNIT PRICE
ITEM CODE
ITEM NAME
QTY
UNIT PRICE
ITEM CODE
ITEM NAME
QTY
UNIT PRICE
ITEM CODE
ITEM NAME
QTY
UNIT PRICE
ITEM CODE
ITEM NAME
QTY
UNIT PRICE
ITEM CODE
ITEM NAME
QTY
UNIT PRICE
SUB TOTAL
TAX
VALUE INCL TAX
DISC
INVOICE VALUE
01-06-2022
123
SUDAR
AB1
ABCD
5
11
AS2
EFGH
14
AK3
VXYZ
14
32
AS1
SKDH
19
AB2
KLOS
17
503
01-01-2020
103
BAGUIATI
AB2
KLOS
18
15
AS5
MNOP
26
11
AS2
EFGH
37
AS1
SKDH
14
23
609
01-01-1995
147
KOLKATA
AS2
EFGH
17
55
AB1
ABCD
17
AK3
VXYZ
23
32
AS5
MNOP
17
AB2
KLOS
23
15
1280
Sr. No
Item Code
Item Name
Item Qty
AB1
ABCD
#VALUE!
AB2
KLOS
#VALUE!
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))
Please Help in this regards
Thanking YOU
YOURS Sincerely
Did you confirm the formula with Ctrl+Shift+Enter? That is essential if you have Excel 2016.
- Respected Hans Vogelaar Sir,
Formula as below as i used
=SUM(IF((ITEM RECEIVED!$E$3:$X$5=B3)*(ITEM RECEIVED!$F$3:$Y$5=C3),ITEM RECEIVED!$G$3:$Z$5))
Which i have in D3 of Sheet2
Eagerly waiting With for YOUR kind help
YOURS Sincerely
TARUN@1964