If, Sumif, aggregate, rows?

A

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

as indicated, the numbers marked in red are the expect results, process and registration fee will be extracted from table 3 and cost will be extracted from table 2; couldn't really pinpoint what formula to use, a few look ups ?

could you help me with any formulas with regard to this?


Table 1
[TABLE="width: 1333"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Serial number[/TD]
[TD]Process fee[/TD]
[TD]Registration fee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Process cost[/TD]
[TD]Registration cost[/TD]
[/TR]
[TR]
[TD="align: right"]123525[/TD]
[TD="align: right"]5245+5647-5475[/TD]
[TD="align: right"]5648[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5245+5647+5475[/TD]
[TD="align: right"]5648[/TD]
[/TR]
[TR]
[TD="align: right"]126542[/TD]
[TD="align: right"]5415[/TD]
[TD="align: right"]4512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124562[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124851[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]124487[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]589581C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]V15689[/TD]
[TD][/TD]
[TD]4526-4562[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4526+4562[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="width: 938"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Serial number[/TD]
[TD]Invoice number[/TD]
[TD]Description[/TD]
[TD]Customer[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]123525[/TD]
[TD="align: right"]1562[/TD]
[TD]Process fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5245[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1895[/TD]
[TD]registration fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5648[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4589[/TD]
[TD]Process fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5647[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MJJK-221[/TD]
[TD]Process fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5475[/TD]
[/TR]
[TR]
[TD="align: right"]126542[/TD]
[TD="align: right"]5652[/TD]
[TD]Process fee_04.18-06.18.2018_SN_126542[/TD]
[TD]B[/TD]
[TD="align: right"]5415[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2452[/TD]
[TD]registration fee_04.18-06.18.2018_SN_126542[/TD]
[TD]B[/TD]
[TD="align: right"]4512[/TD]
[/TR]
[TR]
[TD="align: right"]124562[/TD]
[TD="align: right"]2154[/TD]
[TD]Process fee_04.18-06.18.2018_SN_124562[/TD]
[TD]C[/TD]
[TD="align: right"]5541[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5541[/TD]
[TD]registration fee_04.18-06.18.2018_SN_124562[/TD]
[TD]C[/TD]
[TD="align: right"]412[/TD]
[/TR]
[TR]
[TD="align: right"]124851[/TD]
[TD="align: right"]5412[/TD]
[TD]Process fee_04.18-06.18.2018_SN_124851[/TD]
[TD]D[/TD]
[TD="align: right"]451[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4545[/TD]
[TD]registration fee_04.18-06.18.2018_SN_124851[/TD]
[TD]D[/TD]
[TD="align: right"]2562[/TD]
[/TR]
[TR]
[TD="align: right"]124487[/TD]
[TD="align: right"]4654[/TD]
[TD]Process fee_04.18-06.18.2018_SN_124487[/TD]
[TD]E[/TD]
[TD="align: right"]5412[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]45JJa[/TD]
[TD]registration fee_04.18-06.18.2018_SN_124487[/TD]
[TD]E[/TD]
[TD="align: right"]4515[/TD]
[/TR]
[TR]
[TD]589581C[/TD]
[TD="align: right"]568[/TD]
[TD]Process fee_04.18-06.18.2018_SN_589581C[/TD]
[TD]F[/TD]
[TD="align: right"]4514[/TD]
[/TR]
[TR]
[TD]V15689[/TD]
[TD="align: right"]2018965[/TD]
[TD]registration fee_04.18-06.18.2018_SN_V15689[/TD]
[TD]G[/TD]
[TD="align: right"]4526[/TD]
[/TR]
</tbody>[/TABLE]

Table 3
[TABLE="width: 918"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]Serial number[/TD]
[TD]invoice number[/TD]
[TD]description[/TD]
[TD]customer[/TD]
[TD="align: right"]amount[/TD]
[/TR]
[TR]
[TD="align: right"]123525[/TD]
[TD]12RD000451[/TD]
[TD]Process fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5245[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12MD000541[/TD]
[TD]registration fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5648[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12RD000457[/TD]
[TD]Process fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5647[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12RC000548[/TD]
[TD]Process fee_04.18-06.18.2018_SN_123525[/TD]
[TD]A[/TD]
[TD="align: right"]5475[/TD]
[/TR]
[TR]
[TD="align: right"]126542[/TD]
[TD]12RD000452[/TD]
[TD]Process fee_04.18-06.18.2018_SN_126542[/TD]
[TD]B[/TD]
[TD="align: right"]5415[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12MD000542[/TD]
[TD]registration fee_04.18-06.18.2018_SN_126542[/TD]
[TD]B[/TD]
[TD="align: right"]4512[/TD]
[/TR]
[TR]
[TD="align: right"]124562[/TD]
[TD]12RD000453[/TD]
[TD]Process fee_04.18-06.18.2018_SN_124562[/TD]
[TD]C[/TD]
[TD="align: right"]5541[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12MD000543[/TD]
[TD]registration fee_04.18-06.18.2018_SN_124562[/TD]
[TD]C[/TD]
[TD="align: right"]412[/TD]
[/TR]
[TR]
[TD="align: right"]124851[/TD]
[TD]12RD000454[/TD]
[TD]Process fee_04.18-06.18.2018_SN_124851[/TD]
[TD]D[/TD]
[TD="align: right"]451[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12MD000544[/TD]
[TD]registration fee_04.18-06.18.2018_SN_124851[/TD]
[TD]D[/TD]
[TD="align: right"]2562[/TD]
[/TR]
[TR]
[TD="align: right"]124487[/TD]
[TD]12RD000455[/TD]
[TD]Process fee_04.18-06.18.2018_SN_124487[/TD]
[TD]E[/TD]
[TD="align: right"]5412[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12MD000545[/TD]
[TD]registration fee_04.18-06.18.2018_SN_124487[/TD]
[TD]E[/TD]
[TD="align: right"]4515[/TD]
[/TR]
[TR]
[TD]589581C[/TD]
[TD]12RD000456[/TD]
[TD]Process fee_04.18-06.18.2018_SN_589581C[/TD]
[TD]F[/TD]
[TD="align: right"]4514[/TD]
[/TR]
[TR]
[TD]V15689[/TD]
[TD]12MD000546[/TD]
[TD]registration fee_04.18-06.18.2018_SN_V15689[/TD]
[TD]G[/TD]
[TD="align: right"]4526[/TD]
[/TR]
</tbody>[/TABLE]

any assistance will be greatly appreciated !
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
This would be much easier if you could fill in the serial number on each line. Easy way:

1. Select the serial number column for all of your data rows
2. Go To (F5), Special (Alt+S), select Blanks (K), OK (Enter)
3. Press = [up arrow], Ctrl+Enter

Now each row has its correct serial number associated with this.

From there you can use SUMPRODUCT, for example:

Code: 
I13 =SUMPRODUCT($E$30:$E$43, --($H13=$A$30:$A$43), --(I$12=LEFT($C$30:$C$43, LEN(I$12))))

Where:
* E30:E43 is the Amount column in Table 3
* H13 is the serial number in Table 1
* A30:A43 is the Serial number column in Table 3, now with Serial number for each row
* I12 is the "Process Fee" header in Table 1
* C30:C43 is the Description column in Table 3

Is this possible to do in your situation?

Also, how are you getting the 4562 for V15689? And why are some things - rather than +?
 
Last edited:
Upvote 0
iliace said:
This would be much easier if you could fill in the serial number on each line. Easy way:

1. Select the serial number column for all of your data rows
2. Go To (F5), Special (Alt+S), select Blanks (K), OK (Enter)
3. Press = [up arrow], Ctrl+Enter

Now each row has its correct serial number associated with this.

From there you can use SUMPRODUCT, for example:

Code: 
I13 =SUMPRODUCT($E$30:$E$43, --($H13=$A$30:$A$43), --(I$12=LEFT($C$30:$C$43, LEN(I$12))))

Where:
* E30:E43 is the Amount column in Table 3
* H13 is the serial number in Table 1
* A30:A43 is the Serial number column in Table 3, now with Serial number for each row
* I12 is the "Process Fee" header in Table 1
* C30:C43 is the Description column in Table 3

Is this possible to do in your situation?

Also, how are you getting the 4562 for V15689? And why are some things - rather than +?
Click to expand...


Hi, Thank you for your prompt reply Iliace!

I am a bit confused with the structure of your table, for instance:
* A2:A8 is the SN in table 1
* A13:A26 is the SN in table 2
* A30:A43 is the SN in table 3
* B1 and C1 is the process fee & Registration fee (Revenue) Header in table 1, These figures are to be extratced from Table 3; where RD is the process fee; MD is the Registration fee
* D1 and E1 is the process cost and Registration cost (cost) Header in table 1, These figures are to be extracted from Table 2; no set rules of the invoice numbers
* C13:C26 are the descriptions (cost) in table 2
*C30:C43 are the descriptions (revenues) in table 3
* E13:E26 are the amounts in table 2
* E30:E43 are the amounts in table 3


Sorry the 4562 was a type for V15689; it should be just 4526 for Registration fee; and the "-" are only applicable in the Revenue side as we have also invoice number with RC or MC combination, then we Need to deduct it.

I hope that's a bit more clear now :) Hope to hear from you soon !

[TABLE="width: 632"]
<tbody>[TR]
[TD]Serial number[/TD]
[TD]Process fee[/TD]
[TD]Registration fee[/TD]
[TD]Process cost[/TD]
[TD]Registration cost[/TD]
[/TR]
[TR]
[TD]123525[/TD]
[TD]5245+5647-5475[/TD]
[TD]5648[/TD]
[TD]5245+5647+5475[/TD]
[TD]5648[/TD]
[/TR]
[TR]
[TD]126542[/TD]
[TD]5415[/TD]
[TD]4512[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]124562[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]124851[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]124487[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]589581C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]V15689[/TD]
[TD] [/TD]
[TD]4526[/TD]
[TD] [/TD]
[TD]4526+4562[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
OK, I think I'm with you now. I did copy the tables as you posted, and the rows/columns match, but in my small test I did not want to overwrite your subtotal examples, so I put the calculations in a weird spot. Sorry about that. :confused:

Are you OK with copying down the Serial Number as I suggested?
iliace said:
This would be much easier if you could fill in the serial number on each line. Easy way:

1. Select the serial number column for all of your data rows
2. Go To (F5), Special (Alt+S), select Blanks (K), OK (Enter)
3. Press = [up arrow], Ctrl+Enter
Click to expand...
If not, the formula gets way more complex, and at the same time less reliable (or maybe I just don't know how to do it correctly - you'd have to ask Aladin :) ).

And, just to confirm, if the "invoice number" in Table 3 contains "RC" or "MC", it is a subtraction. If the "invoice number" in Table 3 contains an "RD" or "MD", it is an addition. Are there any similar rules that may not be captured in your sample tables?
 
Upvote 0
iliace said:
OK, I think I'm with you now. I did copy the tables as you posted, and the rows/columns match, but in my small test I did not want to overwrite your subtotal examples, so I put the calculations in a weird spot. Sorry about that. :confused:

Are you OK with copying down the Serial Number as I suggested?

If not, the formula gets way more complex, and at the same time less reliable (or maybe I just don't know how to do it correctly - you'd have to ask Aladin :) ).

And, just to confirm, if the "invoice number" in Table 3 contains "RC" or "MC", it is a subtraction. If the "invoice number" in Table 3 contains an "RD" or "MD", it is an addition. Are there any similar rules that may not be captured in your sample tables?
Click to expand...


Hi Iliace,

There are only similar rules in table3 (revenue). in terms of table 2, it is purely the description that matters, and there are no deduction in table 2 (cost). just addition.

or perhaps you could quote Aladin for me? :)

that would be much appreciated !
 
Upvote 0
I assume you are ok with copying down Serial Number column as I suggested, so that each serial number is located next to the invoice number. In which case, try this:

Code: 
B2:  =SUMPRODUCT($E$29:$E$42*ISNUMBER(FIND("D", $B$29:$B$42)) - $E$29:$E$42*ISNUMBER(FIND("C", $B$29:$B$42)), --($A2=$A$29:$A$42), --(B$1=LEFT($C$29:$C$42, LEN(B$1))))

This should get you all of the process fee for each S/N. It should also work in the Registration fee column.

Post back if you get valid results. The Process/Registration costs would just be simplifications of the above formula.



P.S. Aladin is a master at array formulae, and in general these sorts of problems - among many other things. He is a true legend in the Excel community. Check out his posting history on this message board: https://www.mrexcel.com/forum/members/aladin-akyurek.html
 
Last edited:
Upvote 0
iliace said:
I assume you are ok with copying down Serial Number column as I suggested, so that each serial number is located next to the invoice number. In which case, try this:

Code: 
B2:  =SUMPRODUCT($E$29:$E$42*ISNUMBER(FIND("D", $B$29:$B$42)) - $E$29:$E$42*ISNUMBER(FIND("C", $B$29:$B$42)), --($A2=$A$29:$A$42), --(B$1=LEFT($C$29:$C$42, LEN(B$1))))

This should get you all of the process fee for each S/N. It should also work in the Registration fee column.

Post back if you get valid results. The Process/Registration costs would just be simplifications of the above formula.



P.S. Aladin is a master at array formulae, and in general these sorts of problems - among many other things. He is a true legend in the Excel community. Check out his posting history on this message board: https://www.mrexcel.com/forum/members/aladin-akyurek.html
Click to expand...

Thank you again for your Response. I tried the formula in the B2 cell, it all came back as 0 in cell B2:B8

and I did try a bit of Alternation to the formula as the following:

SUMPRODUCT($E$29:$E$42*ISNUMBER(FIND("RD"; $B$29:$B$42))-$E$29:$E$42*ISNUMBER(FIND("RC"; $B$29:$B$42));($A2=$A$29:$A$42);(B$1=LEFT($C$29:$C$42;LEN(B$1))))

It still came back as 0 :eeek:

Any idea what went wrong?? I am totally lost
 
Upvote 0
iliace said:
I assume you are ok with copying down Serial Number column as I suggested, so that each serial number is located next to the invoice number. In which case, try this:

Code: 
B2:  =SUMPRODUCT($E$29:$E$42*ISNUMBER(FIND("D", $B$29:$B$42)) - $E$29:$E$42*ISNUMBER(FIND("C", $B$29:$B$42)), --($A2=$A$29:$A$42), --(B$1=LEFT($C$29:$C$42, LEN(B$1))))

This should get you all of the process fee for each S/N. It should also work in the Registration fee column.

Post back if you get valid results. The Process/Registration costs would just be simplifications of the above formula.



P.S. Aladin is a master at array formulae, and in general these sorts of problems - among many other things. He is a true legend in the Excel community. Check out his posting history on this message board: https://www.mrexcel.com/forum/members/aladin-akyurek.html
Click to expand...

No clue how to Quote a member on here, perhaps you could help me to Quote him in the discussion.

That would be great!

Thanks.:)
 
Upvote 0
Did you modify the data as I suggested, in the Serial Number column for Table 2 and Table 3? If not, then this approach will not work.

iliace said:
This would be much easier if you could fill in the serial number on each line. Easy way:

1. Select the serial number column for all of your data rows
2. Go To (F5), Special (Alt+S), select Blanks (K), OK (Enter)
3. Press = [up arrow], Ctrl+Enter

Now each row has its correct serial number associated with this.
Click to expand...

This is what I get:

Code: 
S/N	Proc fee	Reg fee
123525	 5,417 	 	5,648 
126542	 5,415 	 	4,512 
124562	 5,541 	 	412 
124851	 451 	 	2,562 
124487	 5,412 	 	4,515 
589581C	 4,514 	 	-   
V15689	 -   	 	4,526

when Table 3 looks like this:
Code: 
Table 3				
S/N	invoice number	description					cust	amount 
123525	12RD000451	Process fee_04.18-06.18.2018_SN_123525		A	 5,245 
123525	12MD000541	registration fee_04.18-06.18.2018_SN_123525	A	 5,648 
123525	12RD000457	Process fee_04.18-06.18.2018_SN_123525		A	 5,647 
123525	12RC000548	Process fee_04.18-06.18.2018_SN_123525		A	 5,475 
126542	12RD000452	Process fee_04.18-06.18.2018_SN_126542		B	 5,415 
126542	12MD000542	registration fee_04.18-06.18.2018_SN_126542	B	 4,512 
124562	12RD000453	Process fee_04.18-06.18.2018_SN_124562		C	 5,541 
124562	12MD000543	registration fee_04.18-06.18.2018_SN_124562	C	 412 
124851	12RD000454	Process fee_04.18-06.18.2018_SN_124851		D	 451 
124851	12MD000544	registration fee_04.18-06.18.2018_SN_124851	D	 2,562 
124487	12RD000455	Process fee_04.18-06.18.2018_SN_124487		E	 5,412 
124487	12MD000545	registration fee_04.18-06.18.2018_SN_124487	E	 4,515 
589581C	12RD000456	Process fee_04.18-06.18.2018_SN_589581C		F	 4,514 
V15689	12MD000546	registration fee_04.18-06.18.2018_SN_V15689	G	 4,526
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Need help with formula to transfer data within multiple sheets
Replies
1
Views
492
Aberdham
A

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top