Need a function to add values whenever there are same truck numbers

[DavidCruz07]

(A) (B) (C) (D) (E) (F) (G) (H) (I) (J) (K)
Truck Small Big Total Result Truck Small Big Total Result
877336 146 3 149 5.7 877336 615 67 682 26.2
843501 1346 94 1440 55.4 311944 400 100 500 35.7
345201 390 7 397 28.4 839133 1331 125 1456 56.0
311944 500 50 550 39.3 307612 582 82 664 47.4


First row(A)= Truck number

Fourth Row(D)= Small + Big
Excel function I currently have
=SUM(B2+C2)

Fifth Row(E)= Fourth Row/14 when Truck number starts with "3*****"
Excel function I currently have
=IF(A1>"800000",D2/26,D2/14)

Fifth Row(E)=Fourth Row/26 when Truck number starts with "8*****"
Excel function I currently have
=IF(A1>"800000",D2/26,D2/14)

Red Text= When the same Truck number appears in both Table 1 (A:E) and Table 2 (G:K)

My question is, is there a formula I can use to add the Fourth row (D) data together from Table 1 and Table 2 and have it divided accordingly by 14 or 26 when the truck numbers appear the same under Table 1 and Table 2

This is what I want the function to do, Since table 1 and table 2 both have the truck number"877336" I want excel to add the values from both fourth row together (D2+J2), so 149+682= 831 and then since the truck number starts with "8*****" have it divided by 26 and display it on the fifth row(E and K) for both tables. so 831/26= 32. So the fifth row (E and K) would display 32 in both Table 1 and Table 2 for Truck "877336"

Similar as to truck number 311944 where it is under both table 1 and table 2. the function would add 550+500= 1050. and divide it by 14 since it starts with "3*****" and display 75 under Results (E and K)

Again I ONLY want the numbers to add when the truck numbers are the same, if not the same truck number then just proceed with the regular formula where it divides it by 14 or 26 depending on the truck number

ALSO Whenever there is a truck number that is the same in both table 1 and table 2 doesn't necessary mean they will both appear in the same column. for example truck number 877336 is position in
A2 in table 1 and G2 in table 2, which IS in same column. However this is not always the case, truck number 311944 is position in A5 and G3, which is in a DIFFERENT column. So I need the function to be able to work with that too. So for example position A4(truck number:311944) would search through G2:G5 for a the same truck number

I've been stuck on trying to get a function to work like this. If you need anymore details to solve my excel problem please let me know

 

[DavidCruz07]

Re: EXCEL PROBLEM Need a function to add values whenever there are same truck numbers

	A	B	C	D	E	F	G	H	I	J	K
1	Truck	Small	Big	Total	Result		Truck	Small	Big	Total	Result
2	877336	146	3	149	5.7		877336	615	67	682	26.2
3	843501	1346	94	1440	55.4		311944	400	100	500	35.7
4	345201	390	7	397	28.4		839133	1331	125	1456	56
5	311944	500	50	550	39.3		307612	582	82	664	47.4

<tbody>
</tbody>

 

[FormR]

Re: EXCEL PROBLEM Need a function to add values whenever there are same truck numbers

Hi, welcome to the forum!

Is this what you mean about getting the total from the 2nd table?

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K	L
1	Truck	Small	Big	Total	Result	New		Truck	Small	Big	Total	Result
2	877336	146	3	149	5.7	831		877336	615	67	682	26.2
3	843501	1346	94	1440	102.9	1440		311944	400	100	500	35.7
4	345201	390	7	397	28.4	397		839133	1331	125	1456	56
5	311944	500	50	550	39.3	1050		307612	582	82	664	47.4
Database

Cell Formulas
Range	Formula
F2	=D2+SUMIFS(K:K,H:H,A2)

 

[DavidCruz07]

Re: EXCEL PROBLEM Need a function to add values whenever there are same truck numbers

Hi, welcome to the forum!

Is this what you mean about getting the total from the 2nd table?

Excel 2013

	A	B	C	D	E	F	G	H	I	J	K	L
1	Truck	Small	Big	Total	Result	New		Truck	Small	Big	Total	Result
2	877336	146	3	149	5.7	831		877336	615	67	682	26.2
3	843501	1346	94	1440	102.9	1440		311944	400	100	500	35.7
4	345201	390	7	397	28.4	397		839133	1331	125	1456	56
5	311944	500	50	550	39.3	1050		307612	582	82	664	47.4

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Database​

Worksheet Formulas Cell Formula F2 =D2+SUMIFS(K:K,H:H,A2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Not quite, I want the function to be in the Result row. Not in a new row like the one you added as "New"
But yes that would be correct how it added the Totals. But after that it would need to be divided by 14 or 26 depending on what the truck number starts with.

For example the after the function added 149 + 682 = 831 it would be divided by 26. 831/26 displaying "32". Its divided by 26 because the truck number began with "8*****"

Same for truck number 311944, 500+550= 1050 then divided by 14 since the truck number began with "3*****" so 1050/14 = 75

So the end result of the table would display the following

	A	B	C	D	E	F	G	H	I	J	K
1	Truck	Small	Big	Total	Result		Truck	Small	Big	Total	Result
2	877336	146	3	149	32		877336	615	67	682	32
3	843501	1346	94	1440	55.4		311944	400	100	500	75
4	345201	390	7	397	28.4		839133	1331	125	1456	56
5	311944	500	50	550	75		307612	582	82	664	47.4

<tbody>
</tbody>

As you can see it should display the same result whenever the truck number is the same for table 1 and table 2

 

[FormR]

Re: EXCEL PROBLEM Need a function to add values whenever there are same truck numbers

Hi, OK - something like this:

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K
1	Truck	Small	Big	Total	Result		Truck	Small	Big	Total	Result
2	877336	146	3	149	32.0		877336	615	67	682	32
3	843501	1346	94	1440	55.4		311944	400	100	500	75
4	345201	390	7	397	28.4		839133	1331	125	1456	56
5	311944	500	50	550	75.0		307612	582	82	664	47.4
Sheet1

Cell Formulas
Range	Formula
E2	=(D2+SUMIFS(J:J,G:G,A2))/IF(LEFT(A2)="8",26,14)

 

[DavidCruz07]

Re: EXCEL PROBLEM Need a function to add values whenever there are same truck numbers

Yes, that's exactly what I needed

Thank you very much! you don't understand how much easier you just made my life haha