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 !
 
Sort by date Sort by votes
iliace said:
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.



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
Click to expand...


I did try a lot of modifications...

and I have the formula as: SUMPRODUCT('Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RD"&"";'Lease-Sales'!$B$462:$B$579))-'Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RC"&"";'Lease-Sales'!$B$462:$B$579));('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579);(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3))))

it came back as "0"

perhaps the formula is wrong?

could you send me your formula?
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Aberdham said:
I did try a lot of modifications...

and I have the formula as: SUMPRODUCT('Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RD"&"";'Lease-Sales'!$B$462:$B$579))-'Lease-Sales'!$G$462:$G$579*ISNUMBER(FIND(""&"RC"&"";'Lease-Sales'!$B$462:$B$579));('Lease GP April'!$B3='Lease-Sales'!$A$462:$A$579);(C$3=LEFT('Lease-Sales'!$D$462:$D$579;LEN(C$3))))

it came back as "0"

perhaps the formula is wrong?

could you send me your formula?
Click to expand...

I think I have managed to get the formula to work on another sheet as it indicated the same number, but on the original sheet, the same formula returns with all "0"

And how about the process cost and registration cost?

Do you also get the result as you wanted?
 
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
489
Aberdham
A

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

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