If, Sumif, aggregate, rows?

Aberdham · Apr 19, 2018

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

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

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

any assistance will be greatly appreciated !

iliace · Apr 19, 2018

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 +?

Aberdham · Apr 20, 2018

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 +?

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 !

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		4526+4562

iliace · Apr 20, 2018

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.

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

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?

Aberdham · Apr 21, 2018

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.

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?

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 !

iliace · Apr 21, 2018

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

Aberdham · Apr 23, 2018

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

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

Any idea what went wrong?? I am totally lost

Aberdham · Apr 23, 2018

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

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.

Aberdham · Apr 24, 2018

Anyone could help?

iliace · Apr 24, 2018

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.

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

If, Sumif, aggregate, rows?

Aberdham

Board Regular

Excel Facts

iliace

Well-known Member

Aberdham

Board Regular

iliace

Well-known Member

Aberdham

Board Regular

iliace

Well-known Member

Aberdham

Board Regular

Aberdham

Board Regular

Aberdham

Board Regular

iliace

Well-known Member

Similar threads

Forum statistics

Share this page

If, Sumif, aggregate, rows?

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock