I cannot solve this one...

[rixcel]

Hi All,
hoping you can help.
I have a spreadsheet with several sheets that have data of students. There are families with 2 or more students and I have formulas that calculate the total cost for each family by combining all the data for siblings in that family. For example, if there are 3 siblings, it will produce the total result for all three in each students row - so there will be three lots of the same data - See Table 1 below. The issue is, I want to have an auto sum of the total and it is adding multiples of the same data where there are 2 or more students from the same family.
Can someone help to create a rule so that it recognises multiples of the same data and just collate it once - it could collect into another (summary? sheet (preferred), similar to Table 2 below.
The unique identifiers that collect the data from the same family are the Surname and Email.
Table 1

Surname	Email	2020	2021	PAYMENTS	CSEF	CREDIT/ DEBIT	CASH PAYOUT	OUTCOME	ACTION
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname3	user3@yahoo.com	$ 490.00	$ 371.00	$ 861.00	$ -	$ 861.00	$ 861.00	OVERPAYMENT	REFUND
Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND

Formula for amount under 2020 column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$I$4:$I$278)
Formula for amount under 2021 column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$J$4:$J$278)
Formula for amount under PAYMENTS column: =C4+D4
Formula for amount under CSEF column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$L$4:$L$278)+SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$K$4:$K$278)
Formula for amount under CREDIT/DEBIT column: =E4+F4
Formula for amount under CASH PAYOUT column: =IF(E4<=1,"-",E4)
Formula for amount under OUTCOME column: =IF(G4>=1,"OVERPAYMENT","UNDERPAYMENT")
Formula for amount under ACTION column: =IF(I4="OVERPAYMENT","REFUND","COLLECT")
Table 2

Surname	Email	2020	2021	PAYMENTS	CSEF	CREDIT/ DEBIT	CASH PAYOUT	OUTCOME	ACTION
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
Surname3	user3@yahoo.com	$ 490.00	$ 371.00	$ 861.00	$ -	$ 861.00	$ 861.00	OVERPAYMENT	REFUND

 

[Toadstool]

Hi Rixcel,

I'm confused by your "Formula for amount under CSEF column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$L$4:$L$278)+SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$K$4:$K$278)" as it appears it's not the sum of two columns, so I'll do as for the previous 2020, 2021 etc.

You say the Surname and Email are the identifier but you were only checking one. I've changed to SUMIFS to check both.

It appears that for the same Surname/email the 2020,2021,CSEF,etc. should all have the same values. If that is the case you could do your SUMIFS then divide by a COUNTIFS to get the original single values. I've done that here but I've also added a couple of "Check" columns which will give WARNING if the calculated value doesn't match the first value found for that Surname/Email or if the number with the same Surname doesn't match the number with the same Email (although some surnames are common so that might mislead).

Here are the sheets:

Rixcel3.xlsx
	G	H	I	J	K	L	M	N	O	P
3	Surname	Email	2020	2021	PAYMENTS	CSEF	CREDIT/ DEBIT	CASH PAYOUT	OUTCOME	ACTION
4	Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
5	Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
6	Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
7	Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
8	Surname3	user3@yahoo.com	$ 490.00	$ 371.00	$ 861.00	$ -	$ 861.00	$ 861.00	OVERPAYMENT	REFUND
9	Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
OUTCOME

Rixcel3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Surname	Email	2020	2021	PAYMENTS	CSEF	CREDIT/ DEBIT	CASH PAYOUT	OUTCOME	ACTION	Check1	Check2	Check3
4	Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
5	Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
6	Surname3	user3@yahoo.com	$ 490.00	$ 371.00	$ 861.00	$ -	$ 861.00	$ 861.00	OVERPAYMENT	REFUND
SUMMARY

Cell Formulas
Range	Formula
F4:F6,C4:D6	C4	=SUMIFS(OUTCOME!I$4:I$278,OUTCOME!$G$4:$G$278,$A4,OUTCOME!$H$4:$H$278,$B4)/COUNTIFS(OUTCOME!$G$4:$G$278,$A4,OUTCOME!$H$4:$H$278,$B4)
E4:E6,G4:G6	E4	=C4+D4
H4:H6	H4	=IF(E4<=1,"-",E4)
I4:I6	I4	=IF(G4>=1,"OVERPAYMENT","UNDERPAYMENT")
J4:J6	J4	=IF(I4="OVERPAYMENT","REFUND","COLLECT")
K4:L6	K4	=IF(C4=INDEX(OUTCOME!I$4:I$278,MATCH($B4,OUTCOME!$H$4:$H$278,0)),"","WARNING")
M4:M6	M4	=IF(COUNTIF(OUTCOME!$G$4:$G$278,OUTCOME!G4)<>COUNTIF(OUTCOME!$H$4:$H$278,OUTCOME!H4),"WARNING","")