Total all in validation list

[Arts]

Hi guys

I was wondering if there was a way (formula) to tally up all of the list in a validation list. I have provided a simple example of what I need

So from the below I have a list created from using data validation and when I choose the subject I need it sums it up as required. (=SUMIF($C$4:$C$9,$F$4,$D$4:$D$9))

Is there a way that I can sum all of the subjects with a formula if I put all in the list or would I need to edit the data to include an extra column and have "all" next to the entries and then throw in an IF statement

List	Subject	Score		List	Total
Maths	Maths	65​		History	100​
English	English	54​
History	History	32​
Chemistry	Maths	76​
	History	68​
	Chemistry	65​

Would this be the best way (add a column with all and then using the below formula)

=IF(SUMIF($D$4:$D$9,$G$4,$E$4:$E$9)=0,SUMIF(C4:C9,G4,E4:E9),SUMIF($D$4:$D$9,$G$4,$E$4:$E$9))

List	Complete	Subject	Score		List	Total
Maths	All	Maths	65​		All	360​
English	All	English	54​
History	All	History	32​
Chemistry	All	Maths	76​
All	All	History	68​
	All	Chemistry	65​

There shouldn't be a case where anything = 0 so this should only be the case when all is selected.

Using excel 365

Thanks as always

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	List	Subject	Score		List	Total
2	Maths	Maths	65		All	360
3	English	English	54
4	History	History	32
5	Chemistry	Maths	76
6	All	History	68
7		Chemistry	65
Sheet6

Cell Formulas
Range	Formula
F2	F2	=SUMIFS(C:C,B:B,IF(E2="All","*",E2))

Cells with Data Validation
Cell	Allow	Criteria
E2	List	=$A$2:$A$6

 

[Arts]

Thanks Fluff ! Does Exactly what I need, didn't know/to think you could put an IF statement in the criteria aspect.

(There is something so simplistic yet complex about what you provided)

 

[Fluff]

You're welcome & thanks for the feedback.