Need a book order formula which counts all students but results in zero if no textbook is needed

[djaida]

Hello all,

I hope the thread title makes it clear what kind of formula I need. I'm trying to develop a spreadsheet for book orders. I have a list of courses and accompanying textbooks for which I need quantities for major courses all students attend together, and minor courses that they attend separately. I have a separate list of students and which major and minor courses they have chosen.

My quantity uses a countif formula to count how many copies of major and minor course textbooks I will need:

Major courses in Column F of the students list:

=COUNTIF($F$20:$F$42;"Computer Science")

Minor coursesin Column G of the students list:

=COUNTIF($G$20:$G$42;"Business")

then another one which adds up the quantity with an additional copy for the instructor and subtracts any copies we already have in stock:

To order (quantity + instructor copy - in stock):

=IF(I5+J5-K5<0;0;I5+J5-K5)

How do I edit the countif formula to result in 0 when an instructor decides they will use other materials instead of a set textbook? Or is there another formula which is better suited to what I need? Thank you in advance!

 

[etaf]

how will excel know that an instructor does not want to use a textbook , is there a column/cell that is noted in ??

 

[djaida]

how will excel know that an instructor does not want to use a textbook , is there a column/cell that is noted in ??

Hi, I check in with the instructors to see if there have been any changes in their syllabi re: textbooks and they tell me if the textbooks are the same, have changed, or been replaced with materials they will be preparing. If there's no textbook, I enter zeroes in the textbook, author, publisher, and ISBN columns.

 

[etaf]

need more info on the data structure , not really explainined the info

BUT you count the textbooks required using
=COUNTIF($F$20:$F$42;"Computer Science")
and
=COUNTIF($G$20:$G$42;"Business")

now you want a zero if no textbook in sylabus
so if you enter zero in the textbook fields - then that would not be counted
as the textbook would be zero and NOT contain "business" or "computer science"

and so return zero

you will need to provide

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[djaida]

need more info on the data structure , not really explainined the info

BUT you count the textbooks required using
=COUNTIF($F$20:$F$42;"Computer Science")
and
=COUNTIF($G$20:$G$42;"Business")

now you want a zero if no textbook in sylabus
so if you enter zero in the textbook fields - then that would not be counted
as the textbook would be zero and NOT contain "business" or "computer science"

and so return zero

you will need to provide

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thank you for the advice - I will need to check if I can do this from my personal computer, as I was trying to solve this while I was still at work. If I don't manage tonight, I will try again at work tomorrow.

 

[etaf]

ok,
i will not be back on forum till sunday (possibly earlier) but plan is sunday

 

[djaida]

need more info on the data structure , not really explainined the info

BUT you count the textbooks required using
=COUNTIF($F$20:$F$42;"Computer Science")
and
=COUNTIF($G$20:$G$42;"Business")

now you want a zero if no textbook in sylabus
so if you enter zero in the textbook fields - then that would not be counted
as the textbook would be zero and NOT contain "business" or "computer science"

and so return zero

you will need to provide

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Close - I count the number of students (based on the major courses and minor courses) for whom I will need to order textbooks by using the countif formula. Hopefully all will be cleared up once the data is shared.

I cannot get the add in to work; Excel keeps blocking it first because of protected view, then once that is disabled it blocks all macros. I will need to share it once I desensitize data and add several other formulas and conditional formatting.

 

[djaida]

Close - I count the number of students (based on the major courses and minor courses) for whom I will need to order textbooks by using the countif formula. Hopefully all will be cleared up once the data is shared.

I cannot get the add in to work; Excel keeps blocking it first because of protected view, then once that is disabled it blocks all macros. I will need to share it once I desensitize data and add several other formulas and conditional formatting.

I spent a few sleepless hours last night because this kept bugging me, and I think I solved most of it, with the result that my solution also brought into focus other problems I needed to solve. Below is the code - I managed to copy install XL2BB on my work computer. I don't know why my home PC absolutely refused to load it.

The names and numbers are all randomly generated.

ORDER CALCULATIONS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5													FOREIGN	SUPPLIERS	LOCAL	SUPPLIERS
6														DIRECT	ORDERS
7		Code	Title	Lecturer	Textbook	Author	Publisher	ISBN	Quantity	Instructor copy	In stock	To order	CEN	PEA	BUY	BOO	FAR	QUOTE	Supplier Final	Price final	Coursebook availability	Shipment tracking	COST
8	Common courses	CS320	Project management	John Doe	Project management	Edwardo Peters	CEN	9780338976063	23	1	0	24	$ 60,00					0	CEN	$ 60,00	Available		$ 1.440,00
9		CS330	Data Science	Jane Doe	Data Science	Erasmo Waters	PEA	9781207342138	23	1	17	7		$ 55,00				0	PEA	$ 55,00	Temporarily unavailable		$ 385,00
10		CS340	Software Engineering	Thomas Harry	Software Engineering	Andres Moon	BUY	9782275762723	23	1	16	8			$ 45,00			0	BUY	$ 45,00	Out of print		$ 360,00
11		LANG322	Presentations	Kate Ryan					0	0	0	0						1	#N/A	$ -			$ -
12	Electrical Engineering	EE321	Electrical Engineering						0	0	0	0						1	#N/A	$ -			$ -
13	Business	EC321	Marketing	Ana Jansen	Marketing	Kenneth Atkins	WYL	9783059458275	23	1	0	24				$ 50,00	$ 45,00	1	FAR	$ 45,00		Ordered	$ 1.080,00
14	Economics	EC322	Macroeconomics	Francis Garnett	Macroeconomics	Jim Gallagher	THI	9784823047075	23	1	0	24				$ 35,00	$ 40,00	1	BOO	$ 35,00		Arrived	$ 840,00
15
16		Surname	Name	Student ID	Programme	Major	Minor	Year	Status	Actions
17	1	Arellano	Dustin	2268970602083	Bachelor Degree Programme	Computer Science	Business	3	Active
18	2	Carson	Dex	4573270028201	Bachelor Degree Programme	Computer Science	Business	3	Active
19	3	Cisneros	Gillian	8516091172652	Bachelor Degree Programme	Computer Science	Business	3	Active
20	4	Cooke	Hugh	8951820536994	Bachelor Degree Programme	Computer Science	Economics	3	Active
21	5	Cuevas	Wade	5716621426047	Bachelor Degree Programme	Computer Science	Business	3	Active
22	6	Daniels	Jack	4857946497807	Bachelor Degree Programme	Computer Science	Business	3	Active
23	7	Davidson	Harley	3829483244566	Bachelor Degree Programme	Computer Science	Business	3	Active
24	8	Donovan	Gwendolen	2723173962826	Bachelor Degree Programme	Computer Science	Economics	3	Active
25	9	Fowler	Sullivan	9987294165174	Bachelor Degree Programme	Computer Science	Business	3	Active
26	10	Friedman	Francis	3608655008199	Bachelor Degree Programme	Computer Science	Economics	3	Active
27	11	Gould	Eliot	5359237907977	Bachelor Degree Programme	Computer Science	Business	3	Active
28	12	Hartman	Nevin	8738017242020	Bachelor Degree Programme	Computer Science	Business	3	Active
29	13	Hull	Marguerite	7011559025579	Bachelor Degree Programme	Computer Science	Economics	3	Active
30	14	Klein	Grey	6814264230181	Bachelor Degree Programme	Computer Science	Business	3	Active
31	15	Mahoney	Luther	6213008074835	Bachelor Degree Programme	Computer Science	Business	3	Active
32	16	Mcintosh	Lillian	8969360918961	Bachelor Degree Programme	Computer Science	Business	3	Active
33	17	Montoya	Verena	9340112694686	Bachelor Degree Programme	Computer Science	Economics	3	Active
34	18	Murillo	Zachary	2836183915869	Bachelor Degree Programme	Computer Science	Economics	3	Active
35	19	Ryan	Meghan	4927742034554	Bachelor Degree Programme	Computer Science	Business	3	Active
36	20	Schaefer	Drew	5448721051560	Bachelor Degree Programme	Computer Science	Business	3	Active
37	21	Spence	Mirabel	4542903322453	Bachelor Degree Programme	Computer Science	Economics	3	Active
38	22	Travis	Orrin	7469732409758	Bachelor Degree Programme	Computer Science	Business	3	Active
39	23	Washington	Isaiah	7161851494079	Bachelor Degree Programme	Computer Science	Economics	3	Active
Listing

Cell Formulas
Range	Formula
I8:I14	I8	=IF(E8="",0,(COUNTIF($F$17:$F$39,"Computer Science")))
J8:J14	J8	=IF(E8="",0,COUNTIF(D8,"<>"))
R8:R14	R8	=IF(AND(ISBLANK(M8), ISBLANK(N8),ISBLANK(O8)),1, 0)
S8:S10	S8	=INDEX(direct_orders,MATCH(T8,M8:O8,0))
T8:T10	T8	=MAX(M8:O8)
S11:S14	S11	=INDEX(local_suppliers,MATCH(T11,P11:Q11,0))
T11:T14	T11	=MIN(P11:Q11)
L8:L14	L8	=IF(I8+J8-K8<0,0,I8+J8-K8)
W8:W14	W8	=L8*T8

Named Ranges
Name	Refers To	Cells
direct_orders	=Listing!$M$7:$O$7	S8:S10
local_suppliers	=Listing!$P$7:$Q$7	S11:S14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:V14	Expression	=$V8="Ordered"	text	NO
S8:S14	Cell Value	contains "PEA"	text	NO
S8:S14	Cell Value	contains "CEN"	text	NO
G8:G14	Cell Value	contains "PEA"	text	NO
G8:G14	Cell Value	contains "CEN"	text	NO
B8:U14	Expression	=$U8="Available"	text	NO
B8:U14	Expression	=$U8="Temporarily unavailable"	text	NO
B8:U14	Expression	=$U8="Print on demand"	text	NO
B8:U14	Expression	=$U8="Out of print"	text	NO
B8:V14	Expression	=$V8="Arrived"	text	NO
B8:V14	Expression	=$V8="Incomplete"	text	NO
B8:V14	Expression	=$V8="Late"	text	NO
P14:Q14	Other Type	Color scale		NO
P13:Q13	Other Type	Color scale		NO
B8:X14	Expression	=$E8=""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J17:J39	List	=Data!$M$2:$M$3
U8:U14	List	=Data!$O$3:$O$6
V8:V14	List	=Data!$P$3:$P$7

If any of you have a better solution, please let me know. As you can see, I have a list of students, in this case third year CS. At the end of 2nd year they choose which minors they will study in 3rd and 4th year. I will be getting this data from the registrar.

I will also get data about courses and textbooks from each department. I need to correctly count the number of students in all departments in year for a given academic year and semester, and major/minor combination, separate the list into quote request sheets based on publishers - two of them we order from directly, for the rest we send the quote request to suppliers who provide us with offers. Those are compared, and the suppliers who has a higher number of cheaper titles gets the whole package. Looking at it all I realize I will most likely need to transfer the list of students to another sheet and refer to it from there. All this is probably best done in a database, but I at least have some experience with Excel; I know nothing about databases. And finally, I will need to create charts displaying the number of titles from each publisher and supplier and the cost of those packages, the cost of package for each department, and the status of the orders - whether a book has been ordered, arrived, arrived but incomplete number of copies, or late.

 

[djaida]

I spent a few sleepless hours last night because this kept bugging me, and I think I solved most of it, with the result that my solution also brought into focus other problems I needed to solve. Below is the code - I managed to copy install XL2BB on my work computer. I don't know why my home PC absolutely refused to load it.

The names and numbers are all randomly generated.

ORDER CALCULATIONS.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5													FOREIGN	SUPPLIERS	LOCAL	SUPPLIERS
6														DIRECT	ORDERS
7		Code	Title	Lecturer	Textbook	Author	Publisher	ISBN	Quantity	Instructor copy	In stock	To order	CEN	PEA	BUY	BOO	FAR	QUOTE	Supplier Final	Price final	Coursebook availability	Shipment tracking	COST
8	Common courses	CS320	Project management	John Doe	Project management	Edwardo Peters	CEN	9780338976063	23	1	0	24	$ 60,00					0	CEN	$ 60,00	Available		$ 1.440,00
9		CS330	Data Science	Jane Doe	Data Science	Erasmo Waters	PEA	9781207342138	23	1	17	7		$ 55,00				0	PEA	$ 55,00	Temporarily unavailable		$ 385,00
10		CS340	Software Engineering	Thomas Harry	Software Engineering	Andres Moon	BUY	9782275762723	23	1	16	8			$ 45,00			0	BUY	$ 45,00	Out of print		$ 360,00
11		LANG322	Presentations	Kate Ryan					0	0	0	0						1	#N/A	$ -			$ -
12	Electrical Engineering	EE321	Electrical Engineering						0	0	0	0						1	#N/A	$ -			$ -
13	Business	EC321	Marketing	Ana Jansen	Marketing	Kenneth Atkins	WYL	9783059458275	23	1	0	24				$ 50,00	$ 45,00	1	FAR	$ 45,00		Ordered	$ 1.080,00
14	Economics	EC322	Macroeconomics	Francis Garnett	Macroeconomics	Jim Gallagher	THI	9784823047075	23	1	0	24				$ 35,00	$ 40,00	1	BOO	$ 35,00		Arrived	$ 840,00
15
16		Surname	Name	Student ID	Programme	Major	Minor	Year	Status	Actions
17	1	Arellano	Dustin	2268970602083	Bachelor Degree Programme	Computer Science	Business	3	Active
18	2	Carson	Dex	4573270028201	Bachelor Degree Programme	Computer Science	Business	3	Active
19	3	Cisneros	Gillian	8516091172652	Bachelor Degree Programme	Computer Science	Business	3	Active
20	4	Cooke	Hugh	8951820536994	Bachelor Degree Programme	Computer Science	Economics	3	Active
21	5	Cuevas	Wade	5716621426047	Bachelor Degree Programme	Computer Science	Business	3	Active
22	6	Daniels	Jack	4857946497807	Bachelor Degree Programme	Computer Science	Business	3	Active
23	7	Davidson	Harley	3829483244566	Bachelor Degree Programme	Computer Science	Business	3	Active
24	8	Donovan	Gwendolen	2723173962826	Bachelor Degree Programme	Computer Science	Economics	3	Active
25	9	Fowler	Sullivan	9987294165174	Bachelor Degree Programme	Computer Science	Business	3	Active
26	10	Friedman	Francis	3608655008199	Bachelor Degree Programme	Computer Science	Economics	3	Active
27	11	Gould	Eliot	5359237907977	Bachelor Degree Programme	Computer Science	Business	3	Active
28	12	Hartman	Nevin	8738017242020	Bachelor Degree Programme	Computer Science	Business	3	Active
29	13	Hull	Marguerite	7011559025579	Bachelor Degree Programme	Computer Science	Economics	3	Active
30	14	Klein	Grey	6814264230181	Bachelor Degree Programme	Computer Science	Business	3	Active
31	15	Mahoney	Luther	6213008074835	Bachelor Degree Programme	Computer Science	Business	3	Active
32	16	Mcintosh	Lillian	8969360918961	Bachelor Degree Programme	Computer Science	Business	3	Active
33	17	Montoya	Verena	9340112694686	Bachelor Degree Programme	Computer Science	Economics	3	Active
34	18	Murillo	Zachary	2836183915869	Bachelor Degree Programme	Computer Science	Economics	3	Active
35	19	Ryan	Meghan	4927742034554	Bachelor Degree Programme	Computer Science	Business	3	Active
36	20	Schaefer	Drew	5448721051560	Bachelor Degree Programme	Computer Science	Business	3	Active
37	21	Spence	Mirabel	4542903322453	Bachelor Degree Programme	Computer Science	Economics	3	Active
38	22	Travis	Orrin	7469732409758	Bachelor Degree Programme	Computer Science	Business	3	Active
39	23	Washington	Isaiah	7161851494079	Bachelor Degree Programme	Computer Science	Economics	3	Active
Listing

Cell Formulas
Range	Formula
I8:I14	I8	=IF(E8="",0,(COUNTIF($F$17:$F$39,"Computer Science")))
J8:J14	J8	=IF(E8="",0,COUNTIF(D8,"<>"))
R8:R14	R8	=IF(AND(ISBLANK(M8), ISBLANK(N8),ISBLANK(O8)),1, 0)
S8:S10	S8	=INDEX(direct_orders,MATCH(T8,M8:O8,0))
T8:T10	T8	=MAX(M8:O8)
S11:S14	S11	=INDEX(local_suppliers,MATCH(T11,P11:Q11,0))
T11:T14	T11	=MIN(P11:Q11)
L8:L14	L8	=IF(I8+J8-K8<0,0,I8+J8-K8)
W8:W14	W8	=L8*T8

Named Ranges
Name	Refers To	Cells
direct_orders	=Listing!$M$7:$O$7	S8:S10
local_suppliers	=Listing!$P$7:$Q$7	S11:S14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:V14	Expression	=$V8="Ordered"	text	NO
S8:S14	Cell Value	contains "PEA"	text	NO
S8:S14	Cell Value	contains "CEN"	text	NO
G8:G14	Cell Value	contains "PEA"	text	NO
G8:G14	Cell Value	contains "CEN"	text	NO
B8:U14	Expression	=$U8="Available"	text	NO
B8:U14	Expression	=$U8="Temporarily unavailable"	text	NO
B8:U14	Expression	=$U8="Print on demand"	text	NO
B8:U14	Expression	=$U8="Out of print"	text	NO
B8:V14	Expression	=$V8="Arrived"	text	NO
B8:V14	Expression	=$V8="Incomplete"	text	NO
B8:V14	Expression	=$V8="Late"	text	NO
P14:Q14	Other Type	Color scale		NO
P13:Q13	Other Type	Color scale		NO
B8:X14	Expression	=$E8=""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J17:J39	List	=Data!$M$2:$M$3
U8:U14	List	=Data!$O$3:$O$6
V8:V14	List	=Data!$P$3:$P$7

If any of you have a better solution, please let me know. As you can see, I have a list of students, in this case third year CS. At the end of 2nd year they choose which minors they will study in 3rd and 4th year. I will be getting this data from the registrar.

I will also get data about courses and textbooks from each department. I need to correctly count the number of students in all departments in year for a given academic year and semester, and major/minor combination, separate the list into quote request sheets based on publishers - two of them we order from directly, for the rest we send the quote request to suppliers who provide us with offers. Those are compared, and the suppliers who has a higher number of cheaper titles gets the whole package. Looking at it all I realize I will most likely need to transfer the list of students to another sheet and refer to it from there. All this is probably best done in a database, but I at least have some experience with Excel; I know nothing about databases. And finally, I will need to create charts displaying the number of titles from each publisher and supplier and the cost of those packages, the cost of package for each department, and the status of the orders - whether a book has been ordered, arrived, arrived but incomplete number of copies, or late.

Upon further testing - adding a more complete list of students from year 1 to 4 to the separate data sheet - I found that I need to adapt the formula: instead of counting the major in column F the way I did when I only had year 3 data, I am now counting the number of the year (1, 2, 3, 4) in column H (Year) on the new data sheet.

=IF(F8="";0;(COUNTIF(Data!$H:$H;"1")))

=IF(F11="";0;(COUNTIF(Data!$H:$H;"2")))

=IF(F16="";0;(COUNTIF(Data!$H:$H;"3")))

=IF(F23="";0;(COUNTIF(Data!$H:$H;"4")))

This works for years 1 and 2 which don't have any minors, and for common courses of years 3 and 4. I don't know how to count if the year is 3 AND the minor is Business or Economics / if the year is 4 AND the minor is Electronic and Electrical Engineering, Business or Economics. How do I incorporate columns G (minor) and H (year) into the above formulas?

 

[djaida]

ok,
i will not be back on forum till sunday (possibly earlier) but plan is sunday

Hello, just wanted to check if you had a chance to see the updates and if you had any ideas on how to incorporate minors and years in the above formulas? It is perhaps something fairly obvious that I cannot see.