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]

We can stick to that;

excellent, may not need any new functions, but at least available

did you see my previous post

 

[djaida]

Ok, so what i see in the data sheet on the share is exactly how its imported
Columns A to K
as there are tables in the sheet from M to T - or does that get extracted as well

Ok, so the one or 2 discrepancies , we can ignore - s o for this exercise we can assume - 1 row = 1 student

We do need for my understanding , and i can see this maybe frustrating , to go back to 1st principles and slowly build it up
As i say away over weekend

I dont see that - ONLY 20 rows with computer science as a Major in the data sheet

there are only 61 rows anyway - including the header
so I cant reconcile your numbers
BUT it would be really good to do that first

having actual expected results is brilliant thankyou - just need to know how that works from the Data Sheet on the share

I dont see that - ONLY 20 rows with computer science as a Major in the data sheet

Right, because I was using only the CS department to try and test the formulas. Now that I have opened and anonymized the main table with all departments and all students, I have noticed a possible complication.

Some courses are 2-semester courses, which means that the textbooks were ordered and arrived in the 1st semester and are used for the entire year. I currently have a hard-coded formula which prints a 0 for that quantity, but like you said hard-coded is not good. In addition to that, some courses have a main textbook and additional readings - those are not ordered for each student but for the library in a few copies. I feel like these additional readings should be separated to another sheet so they don't complicate things.

I have just uploaded the more complete file to Google drive. If I understood correctly, columns important for sorting are A - Programme, B - Academic year, C - Major, D - Minor, E - Year, F - Semester. I'm trying to see what answers I still owe you.

 

[etaf]

observations so far
The name in the Major - needs to be the same as the name in the data file - at the moment its abbreivated - so i changed listing to match the datafile
then i check the numbers and they matched for year , 1,2,3,4 - but we also have year 5 & 6 in the data file
so a simple countif() will find all the Major OK
=COUNTIFS(Data!F:F,Listing!C6,Data!H:H,Listing!E6)

Minor - Business
- Computer Science yr3 = 14
- Political Science and International Relations Yr3 = 2

- Computer Science yr4 = 11
- Political Science and International Relations Yr4 = 8

Minor - Economics
Computer Science yr3 = 8
Computer Science yr4 = 8


Minor - Economics with Business for Y3S2 = 0
Minor - Electrical and Electronic Engineering = 0

Minor
Electronic and Electrical Engineering Yr4 = 3

Minor - Finance Y3S2 = 0

Minor
International law and diplomacy Yr3 = 2
International law and diplomacy Yr4 = 7

Minor - Economics with Business Y4S2 = 0

Minor - Minor Finance Y4S2 = 0

I have inserted some new columns
I have counted those in columns O & P
see the attached file

so just want to check those 2 columns numbers

and now we should have a count of
Major & Minor by Year

so now we can build on that
well at least after the weekend - as this is my last post now for weekend

Dropbox - File Deleted - Simplify your life

www.dropbox.com

we can add the major and Minor together to make 1 formula
=COUNTIFS(Data!F:F,Listing!C6,Data!H:H,Listing!E6) + COUNTIFS(Data!F:F,Listing!C6,Data!H:H,Listing!E6,Data!G:G,Listing!D6)

will combine those 2 and give a quantity for the row
so see column Q

not all rows match - if compared with with column N

the idea is that that formula could be used in column N

now we need to understand why the differences
Column R compares N and Q - True/false - highlighted False RED

 

[etaf]

just getting ready to leave for weekend
ok, i just realised over night , then i may have double counted some books , as i counted for example
computer science - but also there is a book for computer science and for Minor Business
so that entry - Row 19 - title Principles of Marketing, European ed. maybe duplicated
in my forst count i count computer sceince
then 2nd count is for Business
so if computer science and business - the person would be assigned 2 books and not 1

or am i miss-understanding the listing and Minors

at the moment i'm just trying to get the listing and quantity of each book to order correct based on formulas and NOT hardcoding text in - so it applies no matter what the data set
any exceptions we can flag somehow in the listing

anyway - - my totals in the previous maybe wrong because of that
but please go through and explain why they are wrong, so we can first get the totals running correctly

once we have that fully working , we can then move on to all the next things you want

 

[djaida]

just getting ready to leave for weekend
ok, i just realised over night , then i may have double counted some books , as i counted for example
computer science - but also there is a book for computer science and for Minor Business
so that entry - Row 19 - title Principles of Marketing, European ed. maybe duplicated
in my forst count i count computer sceince
then 2nd count is for Business
so if computer science and business - the person would be assigned 2 books and not 1

or am i miss-understanding the listing and Minors

at the moment i'm just trying to get the listing and quantity of each book to order correct based on formulas and NOT hardcoding text in - so it applies no matter what the data set
any exceptions we can flag somehow in the listing

anyway - - my totals in the previous maybe wrong because of that
but please go through and explain why they are wrong, so we can first get the totals running correctly

once we have that fully working , we can then move on to all the next things you want

Hello, sorry for the delay, I came down with fever on Friday evening and was not fit to try and do any serious work until today.

Right, this textbook will appear several times because it is a course attended by students from ECON, CS and PSIR departments. A student can choose minors from their own department or other departments. Your counts would be CS + Business minor, then ECON + Business minor, then PSIR + Business minor.

Give me some time to go over your post #53 and I'll answer the question more completely, but year 5 & 6 are for medical school departments. They are less complicated since they don't have any minors.

I will also apply the formulas in your post to my local file and to the GDrive file and see how they match.

 

[etaf]

ok,
i see however you have 2 more threads on the same sort of subject - not read all of one

Adapt IF AND ISBLANK formula combination in relation to UNIQUE FILTER on another sheet

Hello, my next question has to do with a formula in column QUOTE that will be checked by a UNIQUE FILTER formula and printed on a separate sheet. It currently checks if the two publishers have blank cells and prints the number 1 if they do, and 0 if they don't. If possible, I would like to...

www.mrexcel.com

Organizing data for a textbook order workbook

After a long back and forth with a kind forum member on one of my questions, I realized this is something I should have done first. I need some feedback on the best way to organize the table for my textbook order workbook. This is the newly adapted table; I've added / rearranged the following...

www.mrexcel.com

so it may get confusing if members are helping on another thread and things are changed ......

 

[djaida]

ok,
i see however you have 2 more threads on the same sort of subject - not read all of one

Adapt IF AND ISBLANK formula combination in relation to UNIQUE FILTER on another sheet

Hello, my next question has to do with a formula in column QUOTE that will be checked by a UNIQUE FILTER formula and printed on a separate sheet. It currently checks if the two publishers have blank cells and prints the number 1 if they do, and 0 if they don't. If possible, I would like to...

www.mrexcel.com

Organizing data for a textbook order workbook

After a long back and forth with a kind forum member on one of my questions, I realized this is something I should have done first. I need some feedback on the best way to organize the table for my textbook order workbook. This is the newly adapted table; I've added / rearranged the following...

www.mrexcel.com

so it may get confusing if members are helping on another thread and things are changed ......

Right, but in this thread we are only focusing on the Quantity column - how to get the information from the Data sheet into the appropriate cells without hard-coding, and how to take into account the fact that for some courses the instructors will not be using any textbooks so the quantity in those cells will be zero, regardless of how many students there are in that year.

To answer your question regarding quantities in #53 it is easier for me to count the numbers first for all majors, majors per years, and year 3 and 4 with their minors - that's how I'm used to organizing them into packages, and it makes the most sense to me instead or listing all business minors per different years. I go Department (i.e. their major), year 1-4, minors in years 3 and 4. Then separately medical departments since they have years 1-6 and they don't have minors.

ORDER CALCULATIONS ANON 2023-12-08.xlsx
	L	M
1	Computer Science	89
2	CS Year 1	20
3	CS Year 2	23
4	CS Year 3 all	23
5	CS Year 3 EEE	0
6	CS Year 3 ECON	8
7	CS Year 3 Business	14
8	CS Year 4 all	22
9	CS Year 4 EEE	3
10	CS Year 4 ECON	8
11	CS Year 4 Business	11
Data

Cell Formulas
Range	Formula
M1	M1	=COUNTIF(Table1[[Major]:[Year]],"Computer Science")
M2	M2	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"-",Table1[Year],"1")
M3	M3	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"-",Table1[Year],"2")
M4	M4	=COUNTIFS(Table1[Major],"Computer Science",Table1[Year],"3")
M5	M5	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"3")
M6	M6	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Economics",Table1[Year],"3")
M7	M7	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"3")
M8	M8	=COUNTIFS(Table1[Major],"Computer Science",Table1[Year],"4")
M9	M9	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"4")
M10	M10	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Economics",Table1[Year],"4")
M11	M11	=COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"4")

I know you mentioned previously that it's better to have a range instead of named columns, but whenever I hover over the the range, Excel always picks up the names of the columns, and it's time consuming to change them, so I left the formulas as they are in my local file. In the GDrive file however they are simple ranges.

Now, would it be better to count the number of students per theses criteria, and then reference those cells, or would that just complicate things further? (as an aside, I am thinking about adding an additional column to mark whether a textbook is the main textbook and will be used to calculate the student package, or is additional reading to be ordered in 2 to 5 copies for the library. Additional reading could then be split into a separate sheet and deleted from the main table. So I was thinking that I could use suggestions in how to organize everything, because things I hadn't considered before keep coming up...

 

[djaida]

Hi, unfortunately since this worksheets needs to do many things I keep needing to add to it as I go along. Can we consolidate where we are? I have tried to answer some of your earlier unanswered questions, but I'm not sure I understand them well enough - could you list them as a reply to this post and I'll try again?

Also, looking at your uploaded sheet, I think I can see where this is going! I have added the same columns as you did to my own local sheet and am poking at them to try and understand the formulas and what they are referencing. I also need the formula to take into account whether there's a textbook or not - I assume this will be added once we resolve majors then minors?

 

[etaf]

ok,
i was trying to understand how the count is done - so a formula could be used for any major and any minor - and give an accurate count

i realise I cannot just count computer science as that would be duplicated for minors ,

I guess thats why you use the "-" , which in the listing sheet you have a zero

if we change that to be a dash - OR change the data file - if you manually enter that -

How does the data file show NO minor ?

I think if we are to continue - at least for me to , we needed to take it in very small steps and check as we go along

otherwise , as you are also changing things - its going to be difficult

SO
Q1
I guess thats why you use the "-" , which in the listing sheet you have a zero

Change that to be a dash on the listing - OR change the data file to a zero
How does the data file get presented - you DO NOT want to change that manually - so it can be easily just overwritten

so can the listing be changed to "-" if no minor ?

 

[Tupe77]

I'm not sure I understood the problem correctly. But I thought I would offer my version for testing, if it could be applied to your use.
...If the following assumptions are correct?

1. The Minor value on the data sheet has no effect on the calculation of Major values, because the student takes both Major and Minor courses?
2. When the Minor value of the Listing sheet is <> 0, records where Major=Major,Minor=Minor and Year=Year? are counted from the Data sheet?

=IF(OR(J9="No textbook",H9="Additional reading"),0,(IF(D9=0,COUNTIFS(Data!$F:$F,C9,Data!$H:$H,E9),COUNTIFS(Data!$F:$F,C9,Data!$G:$G,D9,Data!$H:$H,E9))))

Explanations:
If "No testbook" OR "Additional Reading" then the line is not counted. So the result is 0 -> =IF(OR(J9="No textbook",H9="Additional reading"),0,)
If the value of column D of the Listing sheet is 0 (no Minor), then count all records in the Data!$F:$F area (major) where Major=Major and year=year ->IF(D9=0,COUNTIFS(Data!$F:$F,C9,Data!$H:$H,E9)
If the value of column D of the Listing sheet is <> 0 (Minor), then count all records in the Data!$F:$F area (major) where Major=Major and Minor=Minor and year=year -> COUNTIFS(Data!$F:$F,C9,Data!$G:$G,D9,Data!$H:$H,E9)


My apologies for any quirks, English is not my native language.