If, Sumif, aggregate, rows?

[Aberdham]

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

Serial number	Process fee	Registration fee				Process cost	Registration cost
123525	5245+5647-5475	5648				5245+5647+5475	5648
126542	5415	4512
124562
124851
124487
589581C
V15689		4526-4562					4526+4562

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

Table 2

Serial number	Invoice number	Description	Customer	Amount
123525	1562	Process fee_04.18-06.18.2018_SN_123525	A	5245
	1895	registration fee_04.18-06.18.2018_SN_123525	A	5648
	4589	Process fee_04.18-06.18.2018_SN_123525	A	5647
	MJJK-221	Process fee_04.18-06.18.2018_SN_123525	A	5475
126542	5652	Process fee_04.18-06.18.2018_SN_126542	B	5415
	2452	registration fee_04.18-06.18.2018_SN_126542	B	4512
124562	2154	Process fee_04.18-06.18.2018_SN_124562	C	5541
	5541	registration fee_04.18-06.18.2018_SN_124562	C	412
124851	5412	Process fee_04.18-06.18.2018_SN_124851	D	451
	4545	registration fee_04.18-06.18.2018_SN_124851	D	2562
124487	4654	Process fee_04.18-06.18.2018_SN_124487	E	5412
	45JJa	registration fee_04.18-06.18.2018_SN_124487	E	4515
589581C	568	Process fee_04.18-06.18.2018_SN_589581C	F	4514
V15689	2018965	registration fee_04.18-06.18.2018_SN_V15689	G	4526

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Table 3

Serial number	invoice number	description	customer	amount
123525	12RD000451	Process fee_04.18-06.18.2018_SN_123525	A	5245
	12MD000541	registration fee_04.18-06.18.2018_SN_123525	A	5648
	12RD000457	Process fee_04.18-06.18.2018_SN_123525	A	5647
	12RC000548	Process fee_04.18-06.18.2018_SN_123525	A	5475
126542	12RD000452	Process fee_04.18-06.18.2018_SN_126542	B	5415
	12MD000542	registration fee_04.18-06.18.2018_SN_126542	B	4512
124562	12RD000453	Process fee_04.18-06.18.2018_SN_124562	C	5541
	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
	12MD000544	registration fee_04.18-06.18.2018_SN_124851	D	2562
124487	12RD000455	Process fee_04.18-06.18.2018_SN_124487	E	5412
	12MD000545	registration fee_04.18-06.18.2018_SN_124487	E	4515
589581C	12RD000456	Process fee_04.18-06.18.2018_SN_589581C	F	4514
V15689	12MD000546	registration fee_04.18-06.18.2018_SN_V15689	G	4526

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

any assistance will be greatly appreciated !

 

[Aberdham]

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:

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:

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

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?

 

[Aberdham]

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?

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?