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]

please read previous answers , i deliberately added new posts so we can dela with questions separately - you will see the post number on th right - this will be post 31

Looking at the Data sheet , and trying to work out the MAJOR courses , as you used "-" ie NO minor to do some of the count

ANYWAY
lets just focus on the major
we have computer science in D2 on listing sheet
now in the data
we have computer science in column F as a major and year is in H

so
can we just count for major
=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$H:$H,A8)))
so thats all Major
BUT i dont know why that is not working as your example
and do not want to hard code things in, if they exist in the sheet , then that wil be 1 formula that works for all conditions - Otherwise in future this will become an nightmare to update

i added 2 columns - so we can start to breakdown - Major and then Minor and once broken down - we can combine into 1 formula
removed the conditional formatting
trying to strip back to as basic as possibe

BUT lets get each bit correct
here is part of the sheet in xl2bb
and also on dropbox share

ORDER CALCULATIONS ANONYMIZED.xlsx
	J	K
7	Quantity	Major
8	5	5
9	5	5
10	5	5
11	5	5
12	5	5
13	5	5
14	5	5
15	5	5
16	5	5
17	5	5
18	5	5
19	0	0
20	5	5
21	0	0
22	3	5
23	5	5
24	5	5
25	5	5
26	2	5
27	2	5
28	2	5
29	2	5
30	1	5
Listing

Cell Formulas
Range	Formula
J8:J10	J8	=IF(F8="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))
K8:K30	K8	=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$H:$H,A8)))
J11:J15	J11	=IF(F11="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"2")))
J16:J20	J16	=IF(F16="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"3")))
J21	J21	=IF(F21="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Business",Data!$H:$H,"3")))
J22	J22	=IF(F22="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Economics",Data!$H:$H,"3")))
J23:J25	J23	=IF(F23="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"4")))
J26:J27	J26	=IF(F26="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Electronic and Electrical Engineering",Data!$H:$H,"4")))
J28:J29	J28	=IF(F28="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Business",Data!$H:$H,"4")))
J30	J30	=IF(F30="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Economics",Data!$H:$H,"4")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$F8:$F30=""	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Available"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Temporarily unavailable"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Print on demand"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Out of print"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Ordered"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Arrived"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Incomplete"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Late"	text	NO
J8:J30,O8:O30,Y8:Y30	Cell Value	=0	text	NO

Dropbox

www.dropbox.com

 

[djaida]

i just started looking to see how those formulas work on the sheet
and F4 - in the listing sheet is blank and not in example, so has the example now changed

also i for the purposes of this it would be better to use ranges rather than table names and fields - then i know exactly what you are doing - rther than work out that info

I've constantly been testing on my local copy to see if the results would be what I need if I change the contents and I don't always remember to update the uploaded version. For example, if I don't have a lecturer in column E, the quantity in column K changes to 0 and the whole row is greyed out. That signals I need to check with the dean whether an instructor has been chosen. If there's a blank in column F, that signals I need to check with the instructor whether they need a textbook or not. If not, I add "No textbook" in that cell, or fill it in with the required textbook.

Alright, though Excel often automatically turns things into table names and fields, especially if I'm working on the work computer with the latest version - I'm not sure how to turn that off.

 

[djaida]

please read previous answers , i deliberately added new posts so we can dela with questions separately - you will see the post number on th right - this will be post 31

Looking at the Data sheet , and trying to work out the MAJOR courses , as you used "-" ie NO minor to do some of the count

ANYWAY
lets just focus on the major
we have computer science in D2 on listing sheet
now in the data
we have computer science in column F as a major and year is in H

so
can we just count for major
=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$H:$H,A8)))
so thats all Major
BUT i dont know why that is not working as your example
and do not want to hard code things in, if they exist in the sheet , then that wil be 1 formula that works for all conditions - Otherwise in future this will become an nightmare to update

i added 2 columns - so we can start to breakdown - Major and then Minor and once broken down - we can combine into 1 formula
removed the conditional formatting
trying to strip back to as basic as possibe

BUT lets get each bit correct
here is part of the sheet in xl2bb
and also on dropbox share

ORDER CALCULATIONS ANONYMIZED.xlsx
	J	K
7	Quantity	Major
8	5	5
9	5	5
10	5	5
11	5	5
12	5	5
13	5	5
14	5	5
15	5	5
16	5	5
17	5	5
18	5	5
19	0	0
20	5	5
21	0	0
22	3	5
23	5	5
24	5	5
25	5	5
26	2	5
27	2	5
28	2	5
29	2	5
30	1	5
Listing

Cell Formulas
Range	Formula
J8:J10	J8	=IF(F8="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))
K8:K30	K8	=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$H:$H,A8)))
J11:J15	J11	=IF(F11="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"2")))
J16:J20	J16	=IF(F16="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"3")))
J21	J21	=IF(F21="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Business",Data!$H:$H,"3")))
J22	J22	=IF(F22="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Economics",Data!$H:$H,"3")))
J23:J25	J23	=IF(F23="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"4")))
J26:J27	J26	=IF(F26="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Electronic and Electrical Engineering",Data!$H:$H,"4")))
J28:J29	J28	=IF(F28="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Business",Data!$H:$H,"4")))
J30	J30	=IF(F30="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Economics",Data!$H:$H,"4")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$F8:$F30=""	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Available"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Temporarily unavailable"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Print on demand"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$W8="Out of print"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Ordered"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Arrived"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Incomplete"	text	NO
A8:J30,T8:Z30,R9:S11,R13:S15,R17:S19,R21:S23,R28:S30,M8:Q30	Expression	=$X8="Late"	text	NO
J8:J30,O8:O30,Y8:Y30	Cell Value	=0	text	NO

Dropbox

www.dropbox.com

I'm sorry, I just can't seem to be able to follow. Ohhh, the data in D2?? That is not relevant - that small table is only there as a placeholder for working out how to do a drop-down that would change the information shown below so it's easier to input the in stock quantities and price offers once they start arriving. The idea was to select the academic year, and then program, major, year, semester. This is what I see in the content management system when I go to export the information about students - I select the offered choices, click on display to view the data, and export to save it. I then paste that in the data column (or in this case, just a small selection to show what kind of course combinations the students can belong to).

I am going to delete that part so we don't keep getting confused. Having formulas that are locked to D2 will not do anything, that's why I could not understand what you were talking about, and you couldn't understand me.

If you look at the Google drive upload, the headings now start on row 3. I also changed =IF(F8="" to =IF(F8="No textbook" as that makes it more obvious that a textbook is not needed for that course through the use of conditional formatting.

I agree that hard-coding would be a nightmare, though it was a smaller nightmare than having to enter all of these numbers manually. I'm going to have to leave this for tomorrow, because every time I try to go through the previus posts I keep getting more and more tangled up.

 

[etaf]

ok,
i guess this indicates that the sample we are using needs to be accurate and the same for the forum and you

so next step really is set up a sample that is the same as the real information and identical for the forum and you

 

[djaida]

ok,
i guess this indicates that the sample we are using needs to be accurate and the same for the forum and you

so next step really is set up a sample that is the same as the real information and identical for the forum and you

Both my local version and the Google drive have been updated and are now identical. And I should have said that the drop-down options on the top of the sheet were placeholders for when I worked out the formulas in the main table below. That's what I was focusing on. I should have realized that wouldn't be obvious to others.

Now, can you please help me understand, is it possible to work on that drop-down at the same time as on the main table? If that existed in the sheet, would it make it easier to do what I need, or would it complicate things? I thought it was something I should deal with last, and my reasoning for wanting it is that with it I can look at one department at a time, or one program at a time. It has also just occurred to me that having an option to view just the books of a single publisher might also be a good option to include there. That would mean that I wouldn't have to have separate sheets for the two direct order publishers and another for all the rest of the publishers. This might be worth to post as another question on the forum...

 

[djaida]

Actually, would it be a good idea to add two more columns to the beginning of the table - Academic year and Department- in front of the YEAR column on the LISTING sheet?

 

[etaf]

Actually, would it be a good idea to add two more columns to the beginning of the table - Academic year and Department- in front of the YEAR column on the LISTING sheet?

I dont know as i'm still trying to understand what you want and how to apply to spreadsheet
as i say away weekend now and various days before xmas
but i can do my best

I now have the same file as you hopefully

the major - is listed in Column C
so rather than hardcode in the count - that could be referenced
so the fomula instead of
=IF(J6="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))
becomes
=IF(J6="",0,(COUNTIFS(Data!$F:$F,C6,Data!$G:$G,"-",Data!$H:$H,"1")))
And the Year is in column E
so we can use that
=IF(J6="",0,(COUNTIFS(Data!$F:$F,C6,Data!$G:$G,"-",Data!$H:$H,E6)))
Now that will work for any Major and for any year

BUT
in some cases you are using Data!$G:$G,"-",
so i needed to understand how that works and whats its doing

Buy using cells instead of changing every cell to have a specific formula - this will enable the sheet to work with any year and any major

lets take this 1 bit at a time

so just concentrate on counting the Majors and Years ,
i was hoping we can then use something to use for the G lookup - with "-"
But year 3 does not work as i guess we need to think about if they are doing a MINOR , then also need to have a MAJOR

so i have added some columns , so we can breakdown each bit and add to it slowly

on dropbox

column O
just want to work out the Major and number required , using cell references

so just explain that part for now - once we have the Major exact and that will apply no matter whats in the cells and other majors and years
we can then add in the other parts in column P and Q - may need more columns
and then create 1 formula to do the lot
I have added the date and time to the file name so we can keep up with the changes and versions
DATE backwards - 231208 - 8th Dec 23
and time 1049

ALSO post any changes you make to the file in the actual post - so i dont have to look at the whole thread to find the lates updated link

Dropbox

www.dropbox.com

 

[djaida]

I dont know as i'm still trying to understand what you want and how to apply to spreadsheet
as i say away weekend now and various days before xmas
but i can do my best

I now have the same file as you hopefully

the major - is listed in Column C
so rather than hardcode in the count - that could be referenced
so the fomula instead of
=IF(J6="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))
becomes
=IF(J6="",0,(COUNTIFS(Data!$F:$F,C6,Data!$G:$G,"-",Data!$H:$H,"1")))
And the Year is in column E
so we can use that
=IF(J6="",0,(COUNTIFS(Data!$F:$F,C6,Data!$G:$G,"-",Data!$H:$H,E6)))
Now that will work for any Major and for any year

BUT
in some cases you are using Data!$G:$G,"-",
so i needed to understand how that works and whats its doing

Buy using cells instead of changing every cell to have a specific formula - this will enable the sheet to work with any year and any major

lets take this 1 bit at a time

so just concentrate on counting the Majors and Years ,
i was hoping we can then use something to use for the G lookup - with "-"
But year 3 does not work as i guess we need to think about if they are doing a MINOR , then also need to have a MAJOR

so i have added some columns , so we can breakdown each bit and add to it slowly

on dropbox

column O
just want to work out the Major and number required , using cell references

so just explain that part for now - once we have the Major exact and that will apply no matter whats in the cells and other majors and years
we can then add in the other parts in column P and Q - may need more columns
and then create 1 formula to do the lot
I have added the date and time to the file name so we can keep up with the changes and versions
DATE backwards - 231208 - 8th Dec 23
and time 1049

ALSO post any changes you make to the file in the actual post - so i dont have to look at the whole thread to find the lates updated link

Dropbox

www.dropbox.com

Alright, let me first try and answer the question what I want.

I want the worksheet to split the two direct order publishers into separate sheets so I can send that as a price query to the publishers.

Next, I want all the rest of the publishers to be grouped into another sheet so I can send that as a price query to several suppliers, who will each try and offer the best price for the textbooks.

Whoever offers the most textbooks at the lowest price will be awarded the textbook order.

I just figured out how to apply two conditions to the publisher sheets so it counts both the PUBLISHER column and the QUOTE column, and ignores all textbooks that have 0 quantity because we have enough copies in stock (I spoke too soon, it doesn't ignore 0 quantities, will need to try again). I'm about to update the Google drive with those formulas so we're all on the same page. I'll answer other questions in a new reply.

 

[etaf]

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

 

[djaida]

I dont know as i'm still trying to understand what you want and how to apply to spreadsheet
as i say away weekend now and various days before xmas
but i can do my best

I now have the same file as you hopefully

the major - is listed in Column C
so rather than hardcode in the count - that could be referenced
so the fomula instead of
=IF(J6="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))
becomes
=IF(J6="",0,(COUNTIFS(Data!$F:$F,C6,Data!$G:$G,"-",Data!$H:$H,"1")))
And the Year is in column E
so we can use that
=IF(J6="",0,(COUNTIFS(Data!$F:$F,C6,Data!$G:$G,"-",Data!$H:$H,E6)))
Now that will work for any Major and for any year

BUT
in some cases you are using Data!$G:$G,"-",
so i needed to understand how that works and whats its doing

Buy using cells instead of changing every cell to have a specific formula - this will enable the sheet to work with any year and any major

lets take this 1 bit at a time

so just concentrate on counting the Majors and Years ,
i was hoping we can then use something to use for the G lookup - with "-"
But year 3 does not work as i guess we need to think about if they are doing a MINOR , then also need to have a MAJOR

so i have added some columns , so we can breakdown each bit and add to it slowly

on dropbox

column O
just want to work out the Major and number required , using cell references

so just explain that part for now - once we have the Major exact and that will apply no matter whats in the cells and other majors and years
we can then add in the other parts in column P and Q - may need more columns
and then create 1 formula to do the lot
I have added the date and time to the file name so we can keep up with the changes and versions
DATE backwards - 231208 - 8th Dec 23
and time 1049

ALSO post any changes you make to the file in the actual post - so i dont have to look at the whole thread to find the lates updated link

Dropbox

www.dropbox.com

OK, I think the best thing to do is to ignore the formulas that are already there. They don't exist, and I need to figure out how to look into the DATA sheet and count the following:

- the number of students attending year 1 of a given department (CS, ECON, PSIR etc.); year 1 has no minors
- the number of students attending year 2 of a given department (CS, ECON, PSIR etc.); year 2 has no minors
- the number of students attending year 3 of a given department (CS, ECON, PSIR etc.); year 3 has major courses all year attends together, and minors that are attended by different number of students
- the number of students attending year 4 of a given department (CS, ECON, PSIR etc.); year 4 has major courses all year attends together, and minors that are attended by different number of students

Each department offers a different number of minors, and one department may offer those minors to students of their own department and also to students of other departments. A year 3 or 4 CS student major can attend a Business minor together with the ECON major students with a Business minor as well as with PSIR students with the same minor. When ordering the textbooks for that year, we need copies for all courses common to the students of that year (let's say there are 40 total), and a split of different quantities for the different minors (let's say we need 10 EE textbooks, 17 Business textbooks, and 13 Economics textbooks). That's a student's package for that year, e.g.

Year 3 CS major with EE minor package is:
CS322 Business Intelligence
CS323 Software Engineering
ENG324 Reports and Professional Correspondence
EE325 Electrical Engineering

Year 3 CS major with Business minor package is:
CS322 Business Intelligence
CS323 Software Engineering
ENG324 Reports and Professional Correspondence
EC326 Marketing fundamentals


Year 3 CS major with Economics minor package is:
CS322 Business Intelligence
CS323 Software Engineering
ENG324 Reports and Professional Correspondence
EC327 Introduction to Macroecenomics

I also think I should replace the - with a 0 for those years which have no minors because Excel thinks I want to divide things whenever there's a - sign.