NEED HELP I HAD IT FIGURED OUT BUT LOST THE FORMULA

[nathantmoore89]

A	PRODUCT TITLE		4,308	287.2	DAYCODE	DAY 1	318
					DAYCODE	DAY2	2772
					DAYCODE	DAY3	854
					DAYCODE	DAY4	364
							TOTAL COMPLETED	0

AS YOU CAN SOMEWHAT SEE, I HAVE THE PRODUCT LETTER A IN THE TOP LEFT AND THE TOTAL COMPLETED NUMBER BOTTOM RIGHT I BELIEVE ON MY SHEET THE TWO CELLS ARE A6 FOR THE PRODUCT LETTER A AND THE TOTAL IS THE 0 IN CELL I10 I have a whole sheet organized like this for about 40 different products, i need to search for a and return the total completed i have had success with IF INDEX MATCH FUNCTIONS BUT I LOST HOW TO DO IT PLEASE HELP.

 

[jasonb75]

Something like this should do it. You will need to replace "product letter" in the formula with the actual product letter, or a reference to a cell containing the product letter to look for.

=VLOOKUP("TOTAL COMPLETED",INDEX(H:H,MATCH("product letter",A:A,0)):INDEX(I:I,MATCH(1e+100,I:I)),2,0)

 

[Dave Patton]

2 other ideas that you can consider
a) formula =AND(A6="",H6="")*(SUM($H$6:H6)-SUM($I$5:I5))
N.B. A zero will show unless you nest the formula with an if function or
in File Options uncheck Show a zero in cells that have a zero value.

b) Fill the Code in Column A and Use Data | Subtotal
If you import this data, amend the export to show the code for each row.
You can fill the code via formula or VBA; a full description of how to do this is on this forum.
Highlight the range of data including the Headings
Subtotal will default to Each change in Code add total to Col H edit if required
Subtotal 1 will show just the Grand Total
Subtotal 2 will show total by Code shown below
Subtotal 3 will show the detail and the totals

T202108b.xlsm
	A	B	C	D	E	F	G	H	I
1								Show Total
2
3
4
5	Code	Product	Col C	Col D	Col E	Col F	Col G	Col H	Total
6	A	PRODUCT TITLE		4,308	287.2	DAYCODE	DAY 1	318
7						DAYCODE	DAY2	2772
8						DAYCODE	DAY3	854
9						DAYCODE	DAY4	364
10									4308
11	B	PRODUCT TITLE		8	10	DAYCODE	DAY 1	318
12									318
4b

Cell Formulas
Range	Formula
I6:I12	I6	=AND(A6="",H6="")*(SUM($H$6:H6)-SUM($I$5:I5))

With Subtotal N.B. I did not enter the formulas shown; I just selected the range and used Data | Subtotals

T202108b.xlsm
	A	B	C	D	E	F	G	H
5	Code	Product	Col C	Col D	Col E	Col F	Col G	Col H
10	A Total							4308
12	B Total							318
13	Grand Total							4626
14
4bb

Cell Formulas
Range	Formula
H10	H10	=SUBTOTAL(9,H6:H9)
H12	H12	=SUBTOTAL(9,H11:H11)
H13	H13	=SUBTOTAL(9,H6:H11)

 

[nathantmoore89]

=VLOOKUP("TOTAL COMPLETED",INDEX(H:H,MATCH("product letter",A:A,0)):INDEX(I:I,MATCH(1e+100,I:I)),2,0)

If I may ask what is the purpose of the match(1e+100,I:I)) can you explain this a bit so I can learn how to write this in future sheets?

 

[jasonb75]

1e+100 is just a really big number written in scientific notation, (if you want to see how big, type it into excel then format that cell as number instead if general / scientific) it is used with approximate match to find the last row in column I that contains a number. The number used needs to be greater than the maximum value that could be found in the match range, large numbers in scientific format are often used as a failsafe method. In reality a much smaller number is often adequate.