Another SUMIF

[RVDL]

Hi All,

In the attached workflow, "Input 1" contains the following:

SCHOOL_ID	STUDENT_ID
123	ABCD
123	NHYT
456	ABCD
789	NHYT
123	KIQW
789	KIQW

In "Input 3" I have the following data:

S_ID	ST_ID	AMOUNT	FEE
5789	NHYT	5700044	100
5789	KIQW	8816062	207
123	NHYT	9831234	219
123	KIQW	6344282	101
4456	ABCD	9155435	125
4789	NHYT	4327472	42
123	ABCD	2856916	33
5456	ABCD	5852602	250
4789	KIQW	6200616	115
123	ABCD	5137321	144

Some SCHOOL_IDs are split as per "Input 2":

SCHOOL_ID	SPLIT_ID
456	4456
456	5456
789	4789
789	5789

I need the following result:

SCHOOL_ID	STUDENT_ID	AMOUNT	FEE
123	ABCD	7994237	177
123	NHYT	9831234	219
123	KIQW	6344282	101
456	ABCD	15008037	375
789	NHYT	10027516	142
789	KIQW	15016678	322

Row 1: ["123", "ABCD"] - There is no mapping in "Input 2" for "SCHOOL_ID" = "123". So from "Input 3" I sum all the AMOUNT for which "S_ID" = "123" and "ST_ID" = "ABCD". This is 2856916 + 5137321 = 7994237. Same applies to "FEE".

Row 6: ["789", "KIQW"] - From "Input 2" we see that "SCHOOL_ID" = "789" maps to multiple IDs: "4789" and "5789" in this example. So from "Input 3" I sum over "4789" and "5789" for "KIQW". Thus "AMOUNT" equals to 6200616 + 8816062 = 15016678. Same again for "FEE".

Hopefully this is clear. How would I do this?

Thanks.

[MelGibson · Accepted solution]

I hope the below answers your question

[RVDL]

@MelGibson Thank you.