How to add up quantities that are associated ONLY with an "Identifier word" like a SKU or word across 2 or 3 sheets in the same workbook?

[jbillh]

Hi Folks,

So, we're trying to figure out how to add up quantities in multiple cells that contain specific "Identifier words" ONLY in the same row as that specific "identifier word" across 2 or 3 sheets in the same workbook? The identifiers words like "SKU" or "butter" in the example, are not always in the same rows since we download data that changes row positions frequently. The quantities to add up will ALWAYS be in the same row as the identifier words but may not always be in the same column. That said, the column "name" will always be "units-1" or "units-2"

I hope the attached helps make more sense of this!

Thanks Kindly,

Bill

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jbillh]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks Peter! It has been so long since I've been on the platform that I sorta forgot some of that stuff. Take Good Care!

 

[Peter_SSs]

Thanks for updating. We may also need some sample data** to understand the layout but see if this is headed in the right direction.

** I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Sheet2

jbillh.xlsm
	A	B
1	Identifier	Units-1
2	eggs	9
3	butter	3
4	cheese	6
5	butter	2
Sheet2

Sheet3 (which is between Sheet2 and Sheet4) in my sample is empty

Sheet 4

jbillh.xlsm
	A	B	C	D	E	F
1
2
3
4
5
6
7
8					identifier	Units-2
9					butter	4
10					eggs	12
11					cheese	16
12					butter	5
Sheet4

Sheet1

jbillh.xlsm
	A	B
1	butter	14
2	eggs	21
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LET(d,WRAPROWS(TOROW(Sheet2:Sheet4!A$1:Z$1000),2),SUM(FILTER(d,TAKE(d,,1)=A1,0)))

 

[jbillh]

mrExcelExample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	identifier	UNITS-1
2	eggs	9
3	butter	3
4	cheese	6
5	butter	2
6
7					(Separate sheet)
8					identifier	UNITS-2
9					butter	4
10					eggs	12
11					cheese	16
12					butter	5
13
14
15	TOTAL UNITS
16	14
17
18
19
20	We want A16 to equal TOTAL UNITS by adding "butter" in UNITS-1 to UNITS-2 (data found on separate sheet)
21	Butter is the sku identifier. "butter" may appear in any row and units may appear in any collumn where the identifier "butter" is in the same row.
22	The identifiers and UNITS do not maintain an absolute cell position so the quantity of units for each identifier must be locatable by the unique identifier (in this case, "butter")
Sheet1

Cell Formulas
Range	Formula
A16	A16	=B5+B3+F9+F12

 

[jbillh]

Thanks for updating. We may also need some sample data** to understand the layout but see if this is headed in the right direction.

** I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Sheet2

jbillh.xlsm
	A	B
1	Identifier	Units-1
2	eggs	9
3	butter	3
4	cheese	6
5	butter	2
Sheet2

Sheet3 (which is between Sheet2 and Sheet4) in my sample is empty

Sheet 4

jbillh.xlsm
	A	B	C	D	E	F
1
2
3
4
5
6
7
8					identifier	Units-2
9					butter	4
10					eggs	12
11					cheese	16
12					butter	5
Sheet4

Sheet1

jbillh.xlsm
	A	B
1	butter	14
2	eggs	21
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=LET(d,WRAPROWS(TOROW(Sheet2:Sheet4!A$1:Z$1000),2),SUM(FILTER(d,TAKE(d,,1)=A1,0)))

Thanks again Peter! I just finally got XL2BB working and uploaded this mini sheet. Did I do that correctly? So, the current thinking is to have one workbook with three sheets in it. Two of them are data we download each week and on sheet is the output sheet where the quantity of "butter from adding the other two sheets together will be summed up. I hope that makes sense. Thanks Again!

 

[Peter_SSs]

In what way does my suggested formula not work?

Good to see that you have XL2BB working.
If you still need then then with XL2BB, instead of showing us everything in one sheet, please show us the two data sheets separately and also the output sheet with the expected results filled in manually.

 

[jbillh]

In what way does my suggested formula not work?

Good to see that you have XL2BB working.
If you still need then then with XL2BB, instead of showing us everything in one sheet, please show us the two data sheets separately and also the output sheet with the expected results filled in manually.

Hi Peter, I think maybe it "doesn't work" because I don't understand it. Let me play with it a bit more and get back to you. Thanks!

 

[jbillh]

Hi Peter, I think maybe it "doesn't work" because I don't understand it. Let me play with it a bit more and get back to you. Thanks!

Hi Again Peter, well... we actually decided to hire a guy on Fiver to help us walk through the various aspects of what we wanted to do with this and now have a working formula. I sure do appreciate your wisdom and help even so! We just needed some real-time help to better understand things. Take Good Care!

 

[Peter_SSs]

and now have a working formula.

Glad you a resolution. Thanks for letting us know.

Wondering if you would care to post that formula so that

1. Having tried to help I could possibly gain a better understanding of what it was you were trying to do, and
2. Future readers trying to do the same or similar thing might get some use from it?