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!

 

[djaida]

OK,
First i was just trying to get the quantities correct for number of books to order , and be able to apply that to any major and minor - without makign a special formula - if possible
then it wont matter what the major or minor is - you can apply
Hopefully

so i thought , maybe mistakingly - we needed to know the total number of books, in quantity

Right, we will need to know that as well, in the reports sheet of the workbook meant for the deans and management - I used to do that manually, adding cell after cell in a SUM formula, so I'd get an absurdly long number of cells. Once all the departments are added there are about 600 rows for all the courses. You can imagine how inefficient that was. There must be a better way to do it. But to get there, I need the workbook to add the student numbers based on year, major, minor, semester from the exports I get form the uni CMS system. I used to print them and count them, and then add the numbers in the quantity column manually. The only thing I want to add manually once this workbook is figured out is the in stock quantities and the prices I received in offers.

 

[etaf]

ok,
so lets start with that Quantity and see how we build that up

with the detail you have - we could create a reference sheet with that info - year , etc and then use that to do a count if in list , thsat would be useful as if things change and books added , then that reference sheet is all that needs to be update

the data sheet you have , is that how the CMS system exports the file - so that the raw file and we want to use that as is - to avoid manual intervention

computer science as a major
we have 20 entries in the file for computer sceince - so is that the TOTAL for major regardless of YEAR
OR is it likely that there will be duplicates

If that is the case then major by year should be easy to get as a quantity
BUT thats not how it appears

As i say - lets just get the Major quantity without hard coding and using the cells in the listing - so it can appy to any major you want to count

 

[djaida]

ok,
so lets start with that
the data sheet you have , is that how the CMS system exports the file - so that the rw file and we want to use that as is - to avoid manual intervention

computer science as a major
we have 20 entries in the file for computer sceince - so is that the TOTAL for major regardless of YEAR
OR is it likely that there will be duplicates

If that is the case then major by year should be easy to get as a quantity
BUT thats not how it appears

As i say - lets just get the Major quantity without hard coding and using the cells in the listing - so it can appy to any major you want to count

Mostly - there are additional columns once you export as Excel file, but the online view is this:

And once I open the export, these are the columns:

Surname	Name	Student ID	Programme	Major	Minor	Year	University e-mail	Private e-mail	Mobile phone	Current address	Home address	Gender	Birth date	Birth place	Birth country	JMBG	Mother	Father	Home address	Nationality	Enrolment date	Expected end date

I only used the first six columns, from Surname to Year. It might also be useful to add the status column (books paid / not paid) and package column (books picked up / not picked up) to the table in the DATA sheet.

 

[djaida]

Mostly - there are additional columns once you export as Excel file, but the online view is this:

View attachment 103236

And once I open the export, these are the columns:

Surname	Name	Student ID	Programme	Major	Minor	Year	University e-mail	Private e-mail	Mobile phone	Current address	Home address	Gender	Birth date	Birth place	Birth country	JMBG	Mother	Father	Home address	Nationality	Enrolment date	Expected end date

I only used the first six columns, from Surname to Year. It might also be useful to add the status column (books paid / not paid) and package column (books picked up / not picked up) to the table in the DATA sheet.

And I just realized, since there might bee books that will arrive late or not all copies would arrive on time, I might need an option where I can record that a certain students didn't pick up all the books for his package and which are missing. This was done in practice on a separate sheet of paper. Would comments work in this case?

 

[etaf]

It might also be useful to add the status column (books paid / not paid) and package column (books picked up / not picked up) to the table in the DATA sheet.

Ok, lets just get the Quantity right first -
eat the elephant a bit at a time , so we know what to order

But its good to keep a list of all the requirements as we move forward
so keep a note of ALL of those , so we tick each one off as we go and NOT forget something

once you start updating the data file - you wont be able to download again and overwrite ... so maybe we can look at that later depending on your process

You have listed a load of Headings you get from the system
programme - is that useful ??

AND

Enrolment date

Expected end date

are these important -

or do you already manipulate the data in the data sheet

In the past i tend to just use the exported file as it is , and build around that , so then if i download again - i dont have to worry about the raw data - just copy and paste into a data sheet and every thing updates
is this something to consider or do ???
if so , then maybe we need to start with that as a dummy sheet, anomilised - can just delete the contents of column

I have asked a few questions previously - so may need to review some of those in a future post

Is each entry ROW on the file a unique row er student , or can a student appear multiple times

 

[djaida]

Ok, lets just get the Quantity right first -
eat the elephant a bit at a time , so we know what to order

But its good to keep a list of all the requirements as we move forward
so keep a note of ALL of those , so we tick each one off as we go and NOT forget something

once you start updating the data file - you wont be able to download again and overwrite ... so maybe we can look at that later depending on your process

You have listed a load of Headings you get from the system
programme - is that useful ??

AND

Enrolment date

Expected end date

are these important -

or do you already manipulate the data in the data sheet

In the past i tend to just use the exported file as it is , and build around that , so then if i download again - i dont have to worry about the raw data - just copy and paste into a data sheet and every thing updates
is this something to consider or do ???
if so , then maybe we need to start with that as a dummy sheet, anomilised - can just delete the contents of column

I have asked a few questions previously - so may need to review some of those in a future post

Is each entry ROW on the file a unique row er student , or can a student appear multiple times

so keep a note of ALL of those , so we tick each one off as we go and NOT forget something

I will do that, thank you

You have listed a load of Headings you get from the system
programme - is that useful ??

Not for the purposes of orders, so that can be ignored. Same for enrollment date and expected end date. I haven't manipulated data in the data sheet before; earlier I would just print out the lists containing the students divided by years, majors and minors and count them that way. Ideally I'd have a module within the CMS, but that can't be developed any time soon.

just copy and paste into a data sheet and every thing updates. is this something to consider or do ???

Yes, that's what I'm hoping to do - once time comes to start sending price queries to publishers, import the new lists and update the main sheet. We order twice a year, several months before the start of semester for which we need to order books.

I have asked a few questions previously - so may need to review some of those in a future post

I will go back and check what i haven't answered yet - it's easy to lose track of what's been covered and what wasn't!

Is each entry ROW on the file a unique row er student , or can a student appear multiple times

In most cases yes, it's a unique students, though there have been one or two cases where a student enrolled into one department, then once they were a senior enrolled as a first year into another department as a freshman because they wanted both degrees. I don't know how much of an effect that could have - my instinct is that it shouldn't matter but I'm not a data specialist?

 

[djaida]

ok,
so lets start with that Quantity and see how we build that up

with the detail you have - we could create a reference sheet with that info - year , etc and then use that to do a count if in list , thsat would be useful as if things change and books added , then that reference sheet is all that needs to be update

the data sheet you have , is that how the CMS system exports the file - so that the raw file and we want to use that as is - to avoid manual intervention

computer science as a major
we have 20 entries in the file for computer sceince - so is that the TOTAL for major regardless of YEAR
OR is it likely that there will be duplicates

If that is the case then major by year should be easy to get as a quantity
BUT thats not how it appears

As i say - lets just get the Major quantity without hard coding and using the cells in the listing - so it can appy to any major you want to count

we have 20 entries in the file for computer sceince - so is that the TOTAL for major regardless of YEAR

Looking at the export, there are 89 CS majors across all four years. Out of those, 20 are Y1, 24 are Y2, 23 are Y3 (15 Business minors, 8 Economics minors), 22 are Y4 (3 Electronic engineering minors, 11 Business minors, and 8 Economics minors). The file I have online is just a short selection I separated to use as an example and test the formulas .

 

[etaf]

I haven't manipulated data in the data sheet before;

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

In most cases yes, it's a unique students

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

Looking at the export, there are 89 CS majors across all four years.

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

Looking at the export, there are 89 CS majors across all four years. Out of those, 20 are Y1, 24 are Y2, 23 are Y3 (15 Business minors, 8 Economics minors), 22 are Y4 (3 Electronic engineering minors, 11 Business minors, and 8 Economics minors). The file I have online is just a short selection I separated to use as an example and test the formulas .

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

 

[etaf]

just for clarification, you have the following in your profile

Office Version

1. 365
2. 2019
3. 2016

and there is a lot of difference in the functions available in those versions , 365 has a lot of functions which make things much easier to manipulate data - will this be written ONLY to work in 365 version ??????

 

[djaida]

just for clarification, you have the following in your profile


and there is a lot of difference in the functions available in those versions , 365 has a lot of functions which make things much easier to manipulate data - will this be written ONLY to work in 365 version ??????

Just arrived at my destination, was on a train trip. The work PC has the 2019 version installed, I had 2016 on my laptop but I have access to 365 via the work license so I have now installed 365. We can stick to that; I can always print out any reports management wants to see for decision making purposes.