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]

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

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

This works, I think! I replaced the zeroes with dashes, and it got most of the quantities correctly. The only thing it didn't pick up on is years 3 and 4 all students. This is important, because - taking CS as an example - there are three distinct packages:

1. is Y3 common courses + EEE minor,
2. is Y3 common courses + Econ minor,
3. is Y3 common courses + Business minor

In addition to counting the minors, I also need to count all 3rd year students. That's why I had hard-coded formulas when I counted the students on the data sheet:

ORDER CALCULATIONS ANON 2023-12-14.xlsx
	L	M
1	Computer Science	89
2	CS Year 1	20
3	CS Year 2	24
4	CS Year 3 all minors	23
5	CS Year 3 EEE	0
6	CS Year 3 ECON	8
7	CS Year 3 Business	15
8	CS Year 4 all minors	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(F:H,"Computer Science")
M2	M2	=COUNTIFS(F:F,"Computer Science",G:G,"-",H:H,"1")
M3	M3	=COUNTIFS(F:F,"Computer Science",G:G,"-",H:H,"2")
M4	M4	=COUNTIFS(F:F,"Computer Science",H:H,"3")
M5	M5	=COUNTIFS(F:F,"Computer Science",G:G,"Electronic and Electrical Engineering",H:H,"3")
M6	M6	=COUNTIFS(F:F,"Computer Science",G:G,"Economics",H:H,"3")
M7	M7	=COUNTIFS(F:F,"Computer Science",G:G,"Business",H:H,"3")
M8	M8	=COUNTIFS(F:F,"Computer Science",H:H,"4")
M9	M9	=COUNTIFS(F:F,"Computer Science",G:G,"Electronic and Electrical Engineering",H:H,"4")
M10	M10	=COUNTIFS(F:F,"Computer Science",G:G,"Economics",H:H,"4")
M11	M11	=COUNTIFS(F:F,"Computer Science",G:G,"Business",H:H,"4")

How do I count all y3 and y4 students as well with your proposed formula?? ETA and of course I need to take into account the no textbook and additional readings which need to result 0 in the quantity colum (which is for students).

 

[Tupe77]

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

This works, I think! I replaced the zeroes with dashes, and it got most of the quantities correctly. The only thing it didn't pick up on is years 3 and 4 all students. This is important, because - taking CS as an example - there are three distinct packages:

1. is Y3 common courses + EEE minor,
2. is Y3 common courses + Econ minor,
3. is Y3 common courses + Business minor

In addition to counting the minors, I also need to count all 3rd year students. That's why I had hard-coded formulas when I counted the students on the data sheet:

ORDER CALCULATIONS ANON 2023-12-14.xlsx
	L	M
1	Computer Science	89
2	CS Year 1	20
3	CS Year 2	24
4	CS Year 3 all minors	23
5	CS Year 3 EEE	0
6	CS Year 3 ECON	8
7	CS Year 3 Business	15
8	CS Year 4 all minors	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(F:H,"Computer Science")
M2	M2	=COUNTIFS(F:F,"Computer Science",G:G,"-",H:H,"1")
M3	M3	=COUNTIFS(F:F,"Computer Science",G:G,"-",H:H,"2")
M4	M4	=COUNTIFS(F:F,"Computer Science",H:H,"3")
M5	M5	=COUNTIFS(F:F,"Computer Science",G:G,"Electronic and Electrical Engineering",H:H,"3")
M6	M6	=COUNTIFS(F:F,"Computer Science",G:G,"Economics",H:H,"3")
M7	M7	=COUNTIFS(F:F,"Computer Science",G:G,"Business",H:H,"3")
M8	M8	=COUNTIFS(F:F,"Computer Science",H:H,"4")
M9	M9	=COUNTIFS(F:F,"Computer Science",G:G,"Electronic and Electrical Engineering",H:H,"4")
M10	M10	=COUNTIFS(F:F,"Computer Science",G:G,"Economics",H:H,"4")
M11	M11	=COUNTIFS(F:F,"Computer Science",G:G,"Business",H:H,"4")

How do I count all y3 and y4 students as well with your proposed formula??

You haven't tried yet the formula I suggested?

=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))))

 

[etaf]

no, because it was using 0 and i wanted to see if that could be changed to - so we didnt have 2 formula and complicated
as i said in previous post

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

I have not read all your other posts - so will go through later

 

[djaida]

You haven't tried yet the formula I suggested?

=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))))

OK, that works for year 3 and 4 all! Though I'd already replaced the zeroes in column D with a - so I used D9="-" instead, and I had to change column references for no textbook and additional reading because things got shuffled while testing, but comparing it with the hard-coded formulas, the results are identical.

 

[djaida]

no, because it was using 0 and i wanted to see if that could be changed to - so we didnt have 2 formula and complicated
as i said in previous post


I have not read all your other posts - so will go through later

I think perhaps combining the COUNTIFS(Data!$F:$F,C9,Data!$G:$G,D9,Data!$H:$H,E9) with the IF OR formula could also work, giving us two possible solutions for this problem. Let me see if I know how to do that.

Would this be the correct formula?

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

Nope, just tried it.

 

[Tupe77]

OK, that works for year 3 and 4 all! Though I'd already replaced the zeroes in column D with a - so I used D9="-" instead, and I had to change column references for no textbook and additional reading because things got shuffled while testing, but comparing it with the hard-coded formulas, the results are identical.

Glad to hear that the idea of the formula works correctly!
Should there be any further changes to the formula?

 

[djaida]

Glad to hear that the idea of the formula works correctly!
Should there be any further changes to the formula?

I don't think so, but I dare not say no. But at this point in time, I think this is it.

 

[etaf]

But at this point in time, I think this is it.

so its all working now as expected , subject to any other changes
so i will ignore posts now

anything new , maybe suggest starting a new thread with question

 

[djaida]

so its all working now as expected , subject to any other changes
so i will ignore posts now

anything new , maybe suggest starting a new thread with question

Thank you for all the time to invested while I tried to explain what it was I needed. I will be deleting the uploaded table as it won't be needed any more, and if you could do the same it would be appreciated.

 

[Tupe77]

Nice to hear we were able to help, Thanks!
I just removed the workbooks (table) from my computer.