Can array have to conditions to meet before summing the numbers?

[Oberon70]

I have a table with 8,000 transactions, The reference number can appear on several transactions. I am wanting to sum the amount and commission based on the claim number and the invoice number.

Example:

The below has the same claim number but there are different invoices numbers

The end result would be

I have arrays that I have been provided which works great with summing the reference number and then the amounts, but not the above.

thanks,

 

[etaf]

will a SUMIFS() with a UNIQUE()
work

something like

Book1
	A	B	C	D	E	F
1	ref	id	in	am	com	net
2	a	a1	1	100	10	90
3	a	a1	1	200	20	180
4	a	a1	2	300	30	270
5	a	a1	3	400	40	360
6	a	a1	1	500	50	450
7	a	a1	2	600	60	540
8
9
10
11
12
13	a	a1	1	800	80	720
14	a	a1	2	900	90	810
15	a	a1	3	400	40	360
Sheet3

Cell Formulas
Range	Formula
A13:C15	A13	=UNIQUE(A2:C7)
D13:F15	D13	=SUMIFS(D$2:D$7,$A$2:$A$7,$A13,$B$2:$B$7,$B13,$C$2:$C$7,$C13)
Dynamic array formulas.

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[Oberon70]

No problem, after dinner I will upload

 

[Oberon70]

Okay, below is a sample table.

Reference	ID	Agent Name	Amount	Inc Commission	Net	Invoice Num
020137316	1157248	Waynetec	25	3.44	21.56	6995
020137316	1157248	Waynetec	25	3.44	21.56	6995
020137316	1157248	Waynetec	25	3.44	21.56	7101
020137316	1157248	Waynetec	25	3.44	21.56	7209
020137316	1157248	Waynetec	25	3.44	21.56	7433
020137316	1157248	Waynetec	25	3.44	21.56	7433
020146756	1261615	Waynetec	20	2.75	17.25	6879
020146756	1261615	Waynetec	20	2.75	17.25	6879
020146756	1261615	Waynetec	20	2.75	17.25	6879
020146756	1261615	Waynetec	20	2.75	17.25	6879
020146756	1261615	Waynetec	20	2.75	17.25	6995
020146756	1261615	Waynetec	20	2.75	17.25	6995
020146756	1261615	Waynetec	20	2.75	17.25	6995
020146756	1261615	Waynetec	20	2.75	17.25	6995
020146756	1261615	Waynetec	20	2.75	17.25	6995
020146756	1261615	Waynetec	20	2.75	17.25	7101
020146756	1261615	Waynetec	20	2.75	17.25	7101
020146756	1261615	Waynetec	20	2.75	17.25	7101
020146756	1261615	Waynetec	20	2.75	17.25	7209
020146756	1261615	Waynetec	20	2.75	17.25	7209
020146756	1261615	Waynetec	20	2.75	17.25	7209
020146756	1261615	Waynetec	20	2.75	17.25	7209
020146756	1261615	Waynetec	20	2.75	17.25	7209
020146756	1261615	Waynetec	20	2.75	17.25	7321
020146756	1261615	Waynetec	20	2.75	17.25	7321
020146756	1261615	Waynetec	20	2.75	17.25	7321
020146756	1261615	Waynetec	20	2.75	17.25	7321
020146756	1261615	Waynetec	20	2.75	17.25	7433
020146756	1261615	Waynetec	20	2.75	17.25	7433
020146756	1261615	Waynetec	20	2.75	17.25	7433
020146756	1261615	Waynetec	20	2.75	17.25	7433
020178640	1573876	Waynetec	20	2.75	17.25	6879
020178640	1573876	Waynetec	20	2.75	17.25	6879
020178640	1573876	Waynetec	20	2.75	17.25	6995
020178640	1573876	Waynetec	20	2.75	17.25	6995
020178640	1573876	Waynetec	20	2.75	17.25	7101
020178640	1573876	Waynetec	20	2.75	17.25	7101
020178640	1573876	Waynetec	20	2.75	17.25	7209
020178640	1573876	Waynetec	20	2.75	17.25	7209
020178640	1573876	Waynetec	20	2.75	17.25	7321
020178640	1573876	Waynetec	20	2.75	17.25	7321
020178640	1573876	Waynetec	20	2.75	17.25	7433
020178640	1573876	Waynetec	20	2.75	17.25	7433
020178640	1573876	Waynetec	20	2.75	17.25	7433
020181046	1598050	Waynetec	40	5.5	34.5	6879
020181046	1598050	Waynetec	40	5.5	34.5	6995
020181046	1598050	Waynetec	40	5.5	34.5	7101
020181046	1598050	Waynetec	40	5.5	34.5	7209
020181046	1598050	Waynetec	40	5.5	34.5	7321
020181046	1598050	Waynetec	40	5.5	34.5	7433
020188318	1642732	Waynetec	50	6.88	43.12	6879
020188318	1642732	Waynetec	50	6.88	43.12	6995
020188318	1642732	Waynetec	50	6.88	43.12	6995
020188318	1642732	Waynetec	50	6.88	43.12	7101
020188318	1642732	Waynetec	50	6.88	43.12	7101
020188318	1642732	Waynetec	50	6.88	43.12	7101
020188318	1642732	Waynetec	50	6.88	43.12	7209
020188757	1643941	Waynetec	100	13.75	86.25	6995
020188757	1643941	Waynetec	100	13.75	86.25	7101
020188757	1643941	Waynetec	100	13.75	86.25	7209
020188757	1643941	Waynetec	100	13.75	86.25	7321

 

[etaf]

thanks, but thats a picture again
as mentioned in previous post

i did post a simple example with reference, Id & invoice to show how it might be done
and summarised
also a pivot table may work

Also as mentioned it would be a lot easier if you posted using XL2BB or a share, sorry to repeat again, just makes it easier

Did you try the functions i posted on your real data ?

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[Fluff]

thanks, but thats a picture again

Actually it's not. It's a Table view from XL2BB
If you scroll down you will see the copy icon in the bottom left.

 

[Oberon70]

ahh, okay and sorry i will try the function out.

 

[Fluff]

Which column is the invoice number in?
Your image shows it in col 3, but the mini-sheet has it in col 7

 

[Oberon70]

The invoice number can be in any column. When testing out your function I have moved the invoice column to column B. It took me a while to understand the unique function, but it worked great. I have also removed the id number as it won't be needed.

Sorry, below is only an image as I am logged back on my work laptop. Just finish up a few things before going on holiday tomorrow.

 

[Oberon70]

change that I will be putting the ID's numbers back in.