sum with criteria coming from another table

[vince1985]

Good afternoon,

I would like to make a sum of values that meet a specific criteria.
The difficulty that this criteria is not a value but a list of values that correspond to a value that i would enter (this correspondence being inside another table). i think that this request is not very explicit so i have made an example, attached.
In this example, I would like to know the total scores of all the students of a given class (that would be the criteria to input manually). The students with their scores being in one table, and the students and their class number being in another table.
I would like to skip the intermediate step of adding up a column in the score table with the students class number.

Do you know how to deal with this kind of issue ?

thank you in advance for your feedback !

Vincent

 

[alansidman]

Created a parameter query as shown in the attached.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Student	Class number		Student	Score		Class		Student	Class number	Total
2	A	1		C	9		2		C	2	36		Enter the Class in G2
3	B	1		B	17				F	2	31		Click on Data-->RefreshAll
4	C	2		F	18				J	2			Query will update.
5	D	3		G	13				K	2
6	E	1		F	13
7	F	2		B	15
8	G	3		C	13
9	H	3		D	0
10	I	3		A	11
11	J	2		B	13
12	K	2		A	3
13	L	1		C	14
14	M	1
15	N	1
Sheet1

Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(Source, {"Student"}, Source2, {"Student"}, "Tbl", JoinKind.FullOuter),
    #"Expanded Tbl" = Table.ExpandTableColumn(MQ, "Tbl", {"Score"}, {"Score"}),
    #"Grouped Rows" = Table.Group(#"Expanded Tbl", {"Student", "Class number"}, {{"Total", each List.Sum([Score]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Class number] = Table3))

in
    #"Filtered Rows"

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Class = Source{0}[Class]
in
    Class

 

[vince1985]

Thank you very much for your reply ! I will try it now ! Alternatively, do you know whether there are solutions only using Excel functions ?

 

[Peter_SSs]

Is directly like this with a formula what you want?

25 01 26.xlsm
	A	B	C	D	E	F	G	H
1
2	Student	Class		Student	Score		Class	1
3	A	1		C	9		Score	59
4	B	1		B	17
5	C	2		F	18
6	D	3		G	13
7	E	1		F	13
8	F	2		B	15
9	G	3		C	13
10	H	3		D	0
11	I	3		A	11
12	J	2		B	13
13	K	2		A	3
14	L	1		C	14
15	M	1
16	N	1
Class Score

Cell Formulas
Range	Formula
H3	H3	=SUMPRODUCT(SUMIF(D3:D14,A3:A16,E3:E14),--(B3:B16=H2))

 

[vince1985]

i tried both options and both worked very well, thank you very much !!!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.