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]

English is not my native language.

thats useful to know

i was trying to use 1 countifs() - so avoid all the nested IF
no need for the IF , D9 =0
if we can use the value in the minor - to use from the data file

use just a countifs()
COUNTIFS(Data!$F:$F,C9,Data!$G:$G,D9,Data!$H:$H,E9)
Then if instead of zero in D9 it had a "-"
then it would count that info and the minor

the less complicated the formula - the easier to build up as we go and also debug

remember I'm just trying to the very basic counts at this stage , hopefully with 1 formula to count the major and minor
then the exceptions can be added in later

 

[Tupe77]

I tested it in the workbook (ORDER CLULATIONS ANON 2023-12-08 -ETAF1.xlsx) Column S and I thought the results were close to what was requested. That's why I thought to ask about the possible applicability of the formula to this thread.
But as you said, a simpler formula is obviously a better option.

 

[etaf]

ok,
but you have not answerted the question

 

[Tupe77]

ok,
but you have not answerted the question

I didn't understand what was your question?
If you mean the thread's question, then you have misunderstood me. I am not offering a solution to the thread, because this thread is like a never-ending story. Christmas is coming and I'm way too lazy to use it for this project. I proposed a formula only for the Major&Minor calculation for you @etaf , the rest of the project is still up to the two of you.

I didn't mean to interfere with a project you've been working on for a couple of weeks now, I'm sorry if it came across that way . My point was just to suggest one formula to use as part of it.

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[djaida]

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.

Hi, the problem is that some students (CS, ECON, and PSIR 3rd and 4th year) take minor courses, whether from their own department or others. I had hardcoded formulas that I had to insert depending on which year, department etc., they were. I needed to find a formula I could use for that entire column (Quantity) which would correctly read the year, major, minor, whether there is no textbook for that course and whether a book was the main textbook (to be ordered for the students) or additional reading (to be ordered in a couple of copies).

I will try the formula on a copy of the worksheet, thank you!

 

[djaida]

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 ,

Right, there are other departments to consider.

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 ?

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 ?

Right, the "-" comes from the export of students data, but it can be easily changed to 0 as I though having numbers could potentially make it easier to use in formulas.

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

I am sorry for that, but as I try to understand these formulas and how best to organize the data so I can also get the information on the cost of students packages, and each year, and department, as well the total cost of everything I find that I need to make changes as I go along. I hope that answers your question; if there are others I will answer to the best of my ability.

 

[Tupe77]

Hi, the problem is that some students (CS, ECON, and PSIR 3rd and 4th year) take minor courses, whether from their own department or others. I had hardcoded formulas that I had to insert depending on which year, department etc., they were. I needed to find a formula I could use for that entire column (Quantity) which would correctly read the year, major, minor, whether there is no textbook for that course and whether a book was the main textbook (to be ordered for the students) or additional reading (to be ordered in a couple of copies).

I will try the formula on a copy of the worksheet, thank you!

Currently formula calculates the value of "additional reading" rows as 0. How many would "couple of copies" be as number?

 

[djaida]

Currently formula calculates the value of "additional reading" rows as 0. How many would "couple of copies" be as number?

Most commonly one additional copy, but occasionally 2 or 3. The highest number requested was 5, but that's very rare.

 

[Tupe77]

Most commonly one additional copy, but occasionally 2 or 3. The highest number requested was 5, but that's very rare.

Can the number of copies needed be calculated from somewhere? Or is it better to define the result as "additional reading" text or 0?

 

[djaida]

Can the number of copies needed be calculated from somewhere? Or is it better to define the result as "additional reading" text or 0?

Let me think... for that column it's best to leave it at 0 as a result, because that's where the student numbers will be populated. So if a book is additional reading, it's not meant for any one student, but to be used by the lecturer to pull extra material from. Currently the formula in column additional looks at whether there's a lecturer in column I and prints 1 if there is, and 0 if not. I'm planning to transfer all additional readings to a different sheet so I can get a correct price on individual student packages.