Pulling Information (Formulas)

My first thought would be to use the SUMIFS() function. =SUMIFS('Tally Sheet'!$F$2:$F$14,'Tally Sheet'$A$2:$A$14,'Front Sheet'!A3) would return the total count of all records where column A is the same as the value in A3. If you set up data validation on Front Sheet column A, you could then insure that the user cannot enter something that is not found in Tally Sheet.

Did I understand what you are trying to do?

please unmerge cells in your excel file.

you can use the following formula for products name:

PHP Code: 

=XLOOKUP(B3,'Tally Sheet'!$B$2:$B$14,'Tally Sheet'!$A$2:$A$14) 


And for UPC column:

PHP Code: 

=UNIQUE('Tally Sheet'!B2:B14) 


And for Weight column:

PHP Code: 

=SUMIFS('Tally Sheet'!$C$2:$C$14,'Tally Sheet'!$A$2:$A$14,A3,'Tally Sheet'!$B$2:$B$14,B3) 


Two approaches:

Power Query

Please Login or Register  to view this content.

GroupBy Function

PHP Code: 

=GROUPBY(A1:D14,E1:E14,SUM,3) 


Results for each

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Product	Column1	Column2	UPC	Weight	Count	Customer		Product	UPC	Total Weight		Product	Column1	Column2	UPC	Weight
2	NFL Football			90071	9960	24	HOF		NFL Football	90071	34030		College Football	0	0	80071	10790
3	NFL Football			90071	9960	24	HOF		NFL Football	90071A	9960		College Football	0	0	80071M	4980
4	NFL Football			90071	9960	24	HOF		NFL Football	90071J	9960		Highschool Football	0	0	20071	19505
5	NFL Football			90071	4150	10	HOF		College Football	80071	10790		NFL Football	0	0	90071	34030
6	NFL Football			90071A	9960	24	HOF		College Football	80071M	4980		NFL Football	0	0	90071A	9960
7	NFL Football			90071J	9960	24	ATL		Highschool Football	20071	19505		NFL Football	0	0	90071J	9960
8	College Football			80071	9960	24	MIA		Standard Baseball	1422SS	8700		Standard Baseball	0	0	1422SA	8700
9	College Football			80071	830	2	MIA		Standard Baseball	1422SA	8700		Standard Baseball	0	0	1422SS	8700
10	College Football			80071M	4980	12	FTL						Total				106625
11	Highschool Football			20071	9960	24	FPW
12	Highschool Football			20071	9545	23	FPW
13	Standard Baseball			1422SS	8700	60	FPW
14	Standard Baseball			1422SA	8700	60	FPW

Sheet: Tally Sheet

Did you explore the Power Query solution. It automatically updates to any changes to the source document, ie. updates, etc. without having to expand the codes.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Yes, you can use the following formula for products name:

PHP Code: 

=XLOOKUP(1, ('Tally Sheet'!$B$2:$B$19=B3)*('Tally Sheet'!$C$2:$C$19=C3), 'Tally Sheet'!$A$2:$A$19, "") 


And for Color and UPC columns:

PHP Code: 

=UNIQUE('Tally Sheet'!B2:C19) 


And for Weight column:

PHP Code: 

=SUMIFS('Tally Sheet'!$D$2:$D$19,'Tally Sheet'!$A$2:$A$19,A3,'Tally Sheet'!$B$2:$B$19,B3,'Tally Sheet'!$C$2:$C$19,C3)