Sumifs, Sumproduct or is it IF, and Sum

[N0t Y0urs]

I am trying to create a formula that looks at results and levels and then calculates the sum of points.

Tristan Uni.xlsx
	AC	AD	AE	AF	AG	AH
3
4			Completed	Doing	Proposed
5		144 Credit Points	258	0	0
6	100	max 60 @ 100 level
7	200	min 36 @ 300 level
8	400	max 12 @ 400 level
9
Testing

Cell Formulas
Range	Formula
AE5	AE5	=SUM(AI6:AI8)
AF5	AF5	=SUM(AE6:AF8)
AG5	AG5	=SUM(AE6:AG8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AF8:AG8,AI8	Cell Value	>13	text	NO
AF6:AG6,AI6	Cell Value	>61	text	NO

So what I have posted is what I am trying to achieve is from this section of the table. If I update my results and my planned study units that it will calculate my levels/credit points so I can identify what subjects I need and when I will be complete.

Tristan Uni.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2								2021		2022
3	Code	Level	Subject		CP	Master		T1		T1	T2	T3
4	ARTS301	300	Research and Professional Practice	Prescribed	6
5	COMM101	100	Screen Media	Core	6	Y		Dist
6	COMM103	100	Creative Industries	Core	6	Y					Enrol
7	COMM301	300	Film Techniques and Digital Effects	Media Culture	6	Y				HD
8	COMM323	300	Digital and Social Media	Media Culture	6	Y
9	COMM325	300	Television Studies	Media Culture	6	Y					Enrol
10	COMM332	300	News and Journalism	Media Culture	6	Y					Enrol
11	COMM381	300	The Art of Documentary	MC & WP	6	Y
12	COMM382	300	Hollywood Cinema	Media Culture	6	Y				HD
13	COMM385	300	Australian Screens	Media Culture	6	Y
14	COMM387	300	Screen Adapations: Rewriting the text	MC & WP	6	Y
15	COMM389	300	Human Rigts on Screen	Media Culture	6	Y
16	INK101	100	Indigenous Perspectives across Creative Industries	MC & WP	6	Y
17	THEA321	300	Writing for Performance: Stage and Screen	Writing and Publication	6
18	WORK300	300	Work Integrated Learning - Professional Skills Development	Prescribed	6
19	WRIT101	100	The Craft of Acamedic Writing	Core	6			Dist
20	WRIT102	100	Storytelling and Genre Writing	Core	6
21	WRIT303	300	Writing for Reasearch	Prescribed	6
22	WRIT304	300	Writing Short Fiction	Writing and Publication	6
23	WRIT306	300	Writing for Work: Styles and Context	Writing and Publication	6
24	WRIT309	300	Writing Creative Non-fiction	Writing and Publication	6
25	WRIT313	300	Editing Skills and Standards	Writing and Publication	6
26	WRIT326	300	Persuading the Public: Rhetoric in Public Affairs	Writing and Publication	6
27	WRIT327	300	Magazines: Culture, Industry and Practices	MC & WP	6
28	WRIT329	300	Publishing Practice	MC & WP	6					Dist
29
30	COMM00H	400	Communications Honours	Honours	48
31
32	COMM501	500	Film Techniques & Digital Effects	Masters	6
33	COMM523	500	Digital and Social Media	Masters	6
34	COMM525	500	Television Studies	Masters	6
35	COMM532	500	News and Journalism	Masters	6
36	COOM581	500	The Art of Documentary	Masters	6
37	COMM582	500	Hollywood Cinema	Masters	6
38	COMM585	500	Australian Screens	Masters	6
39	COMM587	500	Screen Adaptions: Rewiriting the Text	Masters	6
40	COMM589	500	Human Rights on Screen	Masters	6
41	WRIT503	500	Writing for Research	Masters	6
Testing

I started with SUMIFS but it was totalling all my 100 level subjects, but the moment I added results into the equation it gave me an error. So I tried sum product with summits but then I got spill issues. Where did I go wrong? Are my blank fields causing my problems

 

[Joe4]

Can you post the formulas that you were trying that were giving you issues?

 

[N0t Y0urs]

This is where I started:

Tristan Uni.xlsx
	AI	AJ	AK	AL
3
4	Tring but wrong - Levels
5	258	0	258
6	30
7	120
8	108
9
10
11	Tring but wrong - Results
12	#VALUE!	0	#VALUE!
13	#VALUE!
14	#VALUE!
15	#VALUE!
16	#VALUE!
17	#VALUE!
18	#VALUE!
19
Testing

Cell Formulas
Range	Formula
AI12:AJ12,AI5:AJ5	AJ5	=SUM(AJ6:AJ8)
AK5,AK12	AK5	=SUM(AI6:AK8)
AI6:AI7	AI6	=SUMIFS(CPS,Lvls,AC6)
AI8	AI8	=SUMIFS(CPS,Lvls,">"&399)
AI13:AI16	AI13	=SUMIFS(CPS,Subs,AD11)
AI17:AI18	AI17	=SUMIFS(CPS,Subs,AD17)

Named Ranges
Name	Refers To	Cells
CPS	=Testing!$F$4:$F$41	AI6:AI8, AI13:AI18
Lvls	=Testing!$C$4:$C$41	AI6:AI8
Subs	=Testing!$I$4:$Y$41	AI13:AI18

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI15:AI18	Cell Value	>13	text	NO
AI13	Cell Value	>61	text	NO
AF8:AG8,AI8	Cell Value	>13	text	NO
AF6:AG6,AI6	Cell Value	>61	text	NO

So where I think I am going wrong, in the first formula the SUMIFS of Lvls it is totalling all that equal 100 which on its own is correct but not what I want. The Subs I think the blanks are affecting me but maybe not.

I want to merge the two so basically lets look at a Level of 100. There are 5 subjects that I need to do which give me a total of 30. So If I have a result of HD, List, Credit, or Pass I need to add them and total them. If it says enrolled it will total the enrolled same with plan. From there I move on to the 300 level, 400 level and 500 level.

I know exactly the results but for the life or me I cannot figure out the way to write the formula.

Originally I thought it would be like (in words) "if Lvls = 100 then sumifs subs="HD", CPS, sumifs subs="Dist", CPS" etc but that gave me spill errors

 

[Joe4]

Your formulas reference cells like AD17, but you haven't posted that section, so I have no idea what is in that cell.

It would be most helpful if based on the example data you posted, you could walk me through an example of what you are trying to accomplish, i.e.
what is the exact numeric result you are expecting, and what is the logic (based on your sample data) that gets you there.

Thanks

 

[N0t Y0urs]

Thanks Joe this is what I am working on and to the right my "playing" section

Tristan Uni.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2								2021		2022				2023				2024				2025				Total
3	Code	Level	Subject		CP	Master		T1		T1	T2	T3		T1	T2	T3		T1	T2	T3		T1	T2	T3		48
4	ARTS301	300	Research and Professional Practice	Prescribed	6																									Completed	Doing	Proposed		Tring but wrong - Levels
5	COMM101	100	Screen Media	Core	6	Y		Dist																		6			144 Credit Points	258	0	0		258	0	258
6	COMM103	100	Creative Industries	Core	6	Y					Enrol															6		100	max 60 @ 100 level					30
7	COMM301	300	Film Techniques and Digital Effects	Media Culture	6	Y				HD																6		300	min 36 @ 300 level					120
8	COMM323	300	Digital and Social Media	Media Culture	6	Y																						>399	max 12 @ 400 level					108
9	COMM325	300	Television Studies	Media Culture	6	Y					Enrol															6
10	COMM332	300	News and Journalism	Media Culture	6	Y					Enrol															6			Results
11	COMM381	300	The Art of Documentary	MC & WP	6	Y																							HD	0				Tring but wrong - Results
12	COMM382	300	Hollywood Cinema	Media Culture	6	Y				HD																6			Dist	2				5	3	8
13	COMM385	300	Australian Screens	Media Culture	6	Y																							Cred	0				5	3	0
14	COMM387	300	Screen Adapations: Rewriting the text	MC & WP	6	Y																							Pass	0
15	COMM389	300	Human Rigts on Screen	Media Culture	6	Y
16	INK101	100	Indigenous Perspectives across Creative Industries	MC & WP	6	Y
17	THEA321	300	Writing for Performance: Stage and Screen	Writing and Publication	6																								Enrol		6
18	WORK300	300	Work Integrated Learning - Professional Skills Development	Prescribed	6																								Plan			0
19	WRIT101	100	The Craft of Acamedic Writing	Core	6			Dist																		6
20	WRIT102	100	Storytelling and Genre Writing	Core	6
21	WRIT303	300	Writing for Reasearch	Prescribed	6																										CP's	Completed	Left
22	WRIT304	300	Writing Short Fiction	Writing and Publication	6																								Core	Must Do	24	18	6
23	WRIT306	300	Writing for Work: Styles and Context	Writing and Publication	6																								Prescribed	6 CPs	6		6
24	WRIT309	300	Writing Creative Non-fiction	Writing and Publication	6																								Major	48 CPs	48	30	18
25	WRIT313	300	Editing Skills and Standards	Writing and Publication	6																								Elective	66 CP	66		66
26	WRIT326	300	Persuading the Public: Rhetoric in Public Affairs	Writing and Publication	6
27	WRIT327	300	Magazines: Culture, Industry and Practices	MC & WP	6																								Masters - A Rule - 48 CP
28	WRIT329	300	Publishing Practice	MC & WP	6					Dist																6			max 12 @ 100 level
29																													max 18 @ 200/300 level
30	COMM00H	400	Communications Honours	Honours	48																								min 18 @ 400 or above
31
32	COMM501	500	Film Techniques & Digital Effects	Masters	6																								Masters - B Rule - 48 CP
33	COMM523	500	Digital and Social Media	Masters	6																								max 6 @ 200/300 level
34	COMM525	500	Television Studies	Masters	6																								min 18 @ 400 or above
35	COMM532	500	News and Journalism	Masters	6																								Advance Standings	24 CP
36	COOM581	500	The Art of Documentary	Masters	6
37	COMM582	500	Hollywood Cinema	Masters	6
38	COMM585	500	Australian Screens	Masters	6
39	COMM587	500	Screen Adaptions: Rewiriting the Text	Masters	6
40	COMM589	500	Human Rights on Screen	Masters	6
41	WRIT503	500	Writing for Research	Masters	6
42
Testing

Cell Formulas
Range	Formula
AA3	AA3	=SUM(AA4:AA41)
AE5	AE5	=SUM(AI6:AI8)
AF5	AF5	=SUM(AE6:AF8)
AG5,AK12,AK5	AG5	=SUM(AE6:AG8)
AI12:AJ12,AI5:AJ5	AJ5	=SUM(AJ6:AJ8)
AA5:AA7,AA28,AA19,AA12,AA9:AA10	AA5	=F5
AI6:AI7	AI6	=SUMIFS(CPS,Lvls,AC6)
AI8	AI8	=SUMIFS(CPS,Lvls,">"&399)
AI13	AI13	=COUNTIFS(Subs,$AD$12)+COUNTIFS(Subs,$AD$11)+COUNTIFS(Subs,$AD$13)+COUNTIFS(Subs,$AD$14)
AJ13	AJ13	=COUNTIFS(Subs,$AD$17)
AK13	AK13	=COUNTIFS(Subs,$AD$18)
AE11:AE14	AE11	=COUNTIF(DataRange,AD11)
AF17	AF17	=COUNTIF(DataRange,AD17)
AG18	AG18	=COUNTIF(DataRange,AD18)
AH22:AH25	AH22	=AF22-AG22

Named Ranges
Name	Refers To	Cells
CPS	=Testing!$F$4:$F$41	AI6:AI8
DataRange	=Degree!$I$4:$Y$41	AE11:AE14, AF17, AG18
Lvls	=Testing!$C$4:$C$41	AI6:AI8
Subs	=Testing!$I$4:$Y$41	AI13:AK13

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AK13	Cell Value	>61	text	NO
AJ13	Cell Value	>61	text	NO
AI15:AI18	Cell Value	>13	text	NO
AI13	Cell Value	>61	text	NO
AF8:AG8,AI8	Cell Value	>13	text	NO
AF6:AG6,AI6	Cell Value	>61	text	NO

So the result I want in AE6 based on the above data is 30, then in AF6 the result would be 18.

If I was to change the inputs to the next insert I will include the results.

Tristan Uni.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2								2021		2022				2023				2024				2025				Total
3	Code	Level	Subject		CP	Master		T1		T1	T2	T3		T1	T2	T3		T1	T2	T3		T1	T2	T3		48
4	ARTS301	300	Research and Professional Practice	Prescribed	6										Plan															Completed	Doing	Proposed
5	COMM101	100	Screen Media	Core	6	Y		Dist																		6			144 Credit Points	258	84	120
6	COMM103	100	Creative Industries	Core	6	Y					HD															6		100	max 60 @ 100 level	18	6
7	COMM301	300	Film Techniques and Digital Effects	Media Culture	6	Y				HD																6		300	min 36 @ 300 level	30	6	24
8	COMM323	300	Digital and Social Media	Media Culture	6	Y								Enrol														>399	max 12 @ 400 level	18	6	12
9	COMM325	300	Television Studies	Media Culture	6	Y					HD															6
10	COMM332	300	News and Journalism	Media Culture	6	Y					Dist															6			Results
11	COMM381	300	The Art of Documentary	MC & WP	6	Y										Plan													HD	0
12	COMM382	300	Hollywood Cinema	Media Culture	6	Y				HD																6			Dist	2
13	COMM385	300	Australian Screens	Media Culture	6	Y																							Cred	0
14	COMM387	300	Screen Adapations: Rewriting the text	MC & WP	6	Y																							Pass	0
15	COMM389	300	Human Rigts on Screen	Media Culture	6	Y
16	INK101	100	Indigenous Perspectives across Creative Industries	MC & WP	6	Y
17	THEA321	300	Writing for Performance: Stage and Screen	Writing and Publication	6									Enrol															Enrol		6
18	WORK300	300	Work Integrated Learning - Professional Skills Development	Prescribed	6																								Plan			0
19	WRIT101	100	The Craft of Acamedic Writing	Core	6			Dist																		6
20	WRIT102	100	Storytelling and Genre Writing	Core	6
21	WRIT303	300	Writing for Reasearch	Prescribed	6											Plan															CP's	Completed
22	WRIT304	300	Writing Short Fiction	Writing and Publication	6																								Core	Must Do	24	18
23	WRIT306	300	Writing for Work: Styles and Context	Writing and Publication	6																								Prescribed	6 CPs	6
24	WRIT309	300	Writing Creative Non-fiction	Writing and Publication	6																								Major	48 CPs	48	30
25	WRIT313	300	Editing Skills and Standards	Writing and Publication	6											Plan													Elective	66 CP	66
26	WRIT326	300	Persuading the Public: Rhetoric in Public Affairs	Writing and Publication	6
27	WRIT327	300	Magazines: Culture, Industry and Practices	MC & WP	6																								Masters - A Rule - 48 CP
28	WRIT329	300	Publishing Practice	MC & WP	6					Dist																6			max 12 @ 100 level
29																													max 18 @ 200/300 level
30	COMM00H	400	Communications Honours	Honours	48																								min 18 @ 400 or above
31
32	COMM501	500	Film Techniques & Digital Effects	Masters	6							HD																	Masters - B Rule - 48 CP
33	COMM523	500	Digital and Social Media	Masters	6							Dist																	max 6 @ 200/300 level
34	COMM525	500	Television Studies	Masters	6										Plan														min 18 @ 400 or above
35	COMM532	500	News and Journalism	Masters	6							Dist																	Advance Standings	24 CP
36	COOM581	500	The Art of Documentary	Masters	6											Plan
37	COMM582	500	Hollywood Cinema	Masters	6									Enrol
38	COMM585	500	Australian Screens	Masters	6
39	COMM587	500	Screen Adaptions: Rewiriting the Text	Masters	6
40	COMM589	500	Human Rights on Screen	Masters	6
41	WRIT503	500	Writing for Research	Masters	6
42
Testing

Cell Formulas
Range	Formula
AA3	AA3	=SUM(AA4:AA41)
AE5	AE5	=SUM(AI6:AI8)
AF5	AF5	=SUM(AE6:AF8)
AG5	AG5	=SUM(AE6:AG8)
AA5:AA7,AA28,AA19,AA12,AA9:AA10	AA5	=F5
AE11:AE14	AE11	=COUNTIF(DataRange,AD11)
AF17	AF17	=COUNTIF(DataRange,AD17)
AG18	AG18	=COUNTIF(DataRange,AD18)

Named Ranges
Name	Refers To	Cells
DataRange	=Degree!$I$4:$Y$41	AE11:AE14, AF17, AG18

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG8,AI8	Cell Value	>13	text	NO
AF6:AG6,AI6	Cell Value	>61	text	NO

So I have colour coded the results for ease of reference I think.

 

[Joe4]

I am going to be offline for a few days, so won't be able to look into this until I am back.
If you figure something out in the meantime, please let me know.
Otherwise I will try to look at it when I am back.

 

[N0t Y0urs]

No problem, I appreciate the help

 

[Joe4]

So the result I want in AE6 based on the above data is 30, then in AF6 the result would be 18.

OK, I can get this, with the use of a "helper" column.

I chose column 17, though you can use any column you like.
I entered this formula in cell Z4:

=COUNTBLANK(I4:Y4)

and copied down for all rows (down to Z41).
Following your pattern, I then named range Z4:Z41 "CBS".

The formula for cell AE6 is quite simple, i.e.

=SUMIFS(CPS,Lvls,100)

or

=SUMIFS(CPS,Lvls,AC6)

Then, for cell AF6, you can use:

=SUMIFS(CPS,Lvls,100,CBS,"<>" & 17)

or

=SUMIFS(CPS,Lvls,AC6,CBS,"<>" & 17)

There may be a way to do it without the use of a helper column, but that is more advanced, and I have not done too much like that before.

 

[N0t Y0urs]

thank you, I think that is where I was going around the bend too. I was trying to do it all in one step rather than break it up

 

[Joe4]

thank you, I think that is where I was going around the bend too. I was trying to do it all in one step rather than break it up

It may be possible to do it all in one step, but it is beyond my level of knowledge!