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]

sorry, been really busy sorting out issues at home with leaks and plumbing - not had a chance to look at the full details

No problem, I'll continue digging on my own. The main issue I had is solved I think, but it has of course brought to light others that need looking into. I'll see which post I'll need to mark as solved and try to reformulate any other questions I have so I can get solutions for those.

I hope all is well now with your home!

 

[djaida]

Let me try to get this question solved as well. To clarify, I have a QUANTITY column which needs to be populated with quantities based on a separate list of students from years 1 to 6, from several departments. CS, ECON and PS students attend years 1 to 4, with years 3 and 4 also attending minor courses. Medical students do not have minor courses, though they sometimes have electives, and attend years 1 to 6. There are currently only courses listing CS department as examples.

I've added the formulas suggested in other threads to the workbook uploaded to Google drive as what I'm trying to solve next it depends on other sheets. I hope I'll be able to get some help with this!

 

[etaf]

you will need to give more detail/explanation fro me - with examples
i have downloaded the file

i see in DATA sheet
you have student name
Major column
minor Column

So for
major
computer science = 20
minor
Economics Minor = 4

i'm just not following any of your formulas and cant see why you have what you have
really sorry

 

[djaida]

you will need to give more detail/explanation fro me - with examples
i have downloaded the file

i see in DATA sheet
you have student name
Major column
minor Column

So for
major
computer science = 20
minor
Economics Minor = 4

i'm just not following any of your formulas and cant see why you have what you have
really sorry

Hi, let me see if I can clarify it further. So the main sheet contains a list of all the courses we offer, for all students of all departments. I send availability and price queries to some publishers directly, and to others via their suppliers for our region. That's why I have price comparison in some rows and single prices for others.

Currently there's only CS textbooks on the list but there are hundreds of courses, some of which are majors and other minors.

I usually add quantities for each course manually, by counting how many students are attending each course. That is a tedious process and open to errors. Once all the data is entered, the query needs to be split into three: one for each publisher, and one for the two suppliers.

I would like to pull the student data into the main sheet column quantities based on the year, course major and minor so I don't have to add those numbers one by one. I want the supplier prices to be compared and the cheaper one marked, and then totals compared because that will be used by management to award the contract to the supplier. For that I need reports.

Once the suppliers are selected, I need to keep track of the shipping and arrival (complete, late, how many copies are expected if late, etc.) and finally, a way to mark if the student has picked up their package. I hope it is clearer now. If there are more questions, please let me know!

 

[etaf]

thanks - I did understand the basic requirement

I would like to pull the student data into the main sheet column quantities based on the year, course major and minor so I don't have to add those numbers one by one. I want the supplier prices to be compared and the cheaper one marked, and then totals compared because that will be used by management to award the contract to the supplier. For that I need reports.

What i did not understand are the very specific sheets and columns to use and apply the criteris - thats the detail i'm after

i'm assuming ALL the data is on the DATA Sheet

are All the TITLE Column Column C - all the book titles - used for Computer Science, because that is in D2
so we only count based on that criteria
=countif( data!F:F, D2)
Which will only count criteria in column F of data that has computer science in - so that counts 20 in your example
I dont understand how or why you are now splitting the numbers down - based on column G
which has a - and returns 10 , and year 1 = 5
and the formula appears to show that hard coded

=IF(F8="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))

so that could be changed to
=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$G:$G,"-",Data!$H:$H,A8)))

and now will pick up the subject from D2 and the year from A
so with no minor - use the -

row 20 on main sheet has
=IF(F20="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"3")))

BUT not using column G now - why
row 22
=IF(F22="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Economics",Data!$H:$H,"3")))
now looking at minor

row 16
=IF(F16="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"3")))
no G again

so i dont follow the detail

its almost like every row has a unique hard coded formula

sorry - trying to follow - But failing - it may simply be my understanding

 

[djaida]

thanks - I did understand the basic requirement


What i did not understand are the very specific sheets and columns to use and apply the criteris - thats the detail i'm after

i'm assuming ALL the data is on the DATA Sheet

are All the TITLE Column Column C - all the book titles - used for Computer Science, because that is in D2
so we only count based on that criteria
=countif( data!F:F, D2)
Which will only count criteria in column F of data that has computer science in - so that counts 20 in your example
I dont understand how or why you are now splitting the numbers down - based on column G
which has a - and returns 10 , and year 1 = 5
and the formula appears to show that hard coded

=IF(F8="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))

so that could be changed to
=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$G:$G,"-",Data!$H:$H,A8)))

and now will pick up the subject from D2 and the year from A
so with no minor - use the -

row 20 on main sheet has
=IF(F20="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"3")))

BUT not using column G now - why
row 22
=IF(F22="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"Economics",Data!$H:$H,"3")))
now looking at minor

row 16
=IF(F16="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$H:$H,"3")))
no G again

so i dont follow the detail

its almost like every row has a unique hard coded formula

sorry - trying to follow - But failing - it may simply be my understanding

Hello, yes, I think there is some misunderstanding. I need the specific sheets and formulas to populate them so I don't have to cut and paste various rows into separate documents based on publishers and suppliers manually; that way I don't have to worry if I pasted something over another row or cut something that should have stayed.

No, this is not the entire data. There are many more students - this is just a small selection I can use as an example and as a way to check whether the formulas I'm testing are working. The LISTING sheet also only has the CS textbooks as an example - there are also textbooks for the ECON and PSIR and other departments.

The formulas in the QUANTITY column are indeed hard-coded now because I don't know enough to try and take all the criteria into account and have the results I need. That's why I am looking for a way to combine them.

Some of the rows are not looking in column G (Minors) because the students of those years do not have minors yet. They choose their minors at the end of year 2 and start attending them in years 3 and 4. So they have some common courses and they attend those classes together, then they split into groups according to their chosen minors and attend those lectures separately.

In the Listing sheet, rows 12-15 are common courses for year 3. All year 3 students attend those classes together.
Course in row 16 is an Electronic engineering minor. No students chose that, so the quantity in column J is 0.
Course in row 17 is a Business minor. In this example, half of all students of year 3 are taking Business as a minor. I need the formula to correctly count all year 3 students who are taking that minor from the data sheet and insert that number there.
Course in row 18 is an Economics minor. In this example, half of all students of year 3 are taking Economics as a minor. I need the formula to correctly count all year 3 students who are taking that minor from the data sheet and insert that number there.

Is there a way to get rid of the hard-coding and have a *single* formula that could be applied to the entire column but that would recognize there are several criteria that needs to be taken into account to get the correct quantities? I want to count the quantity of each book based on:

- which departments students belong to,
- which year they are,
- which common courses and
- which minors they are attending in years 3 and 4.

In other words, can the formula on the main sheet in column QUANTITY be from the array A1:J61 on the DATA sheet, and fill in the quantities based on the above criteria? E.g. Year 1 CS, Year 2 CS, Year 3 CS all, Year 3 CS EEE, Year 3 CS ECON, Year 3 CS Business, Year 4 CS EEE, Year 4 CS ECON, Year 4 CS Business?

If there are 40 year 3 CS students, I need to order 40 textbooks for all of them for their common courses. But I *don't* need to order 40 textbooks for their EE, ECON and Business minors if 10 students are attending EE, 18 are attending ECON minor courses, and 12 are attending Business minor courses.

If necessary, I can create another thread and try to break down this question even further and someone else can try to answer it. I have tried to clarify it as much as possible.

 

[etaf]

i think this can be done with a change in some of the way the data file is populated , and a incremental approach adding bits and checking they work ok

as i have already started to do with the countifs() formulas - you can also add countifs() together
which may be what you need to do - one for the major and then another to add minors

personally the more people who get visibility the better support you will get , at the moment , its just me as we are now on page 3 of this question

but a good sample spreadsheet of how the data is laid out with all possibilities - so when you apply to real data , it has not changed in anyway

 

[djaida]

i think this can be done with a change in some of the way the data file is populated , and a incremental approach adding bits and checking they work ok

as i have already started to do with the countifs() formulas - you can also add countifs() together
which may be what you need to do - one for the major and then another to add minors

personally the more people who get visibility the better support you will get , at the moment , its just me as we are now on page 3 of this question

but a good sample spreadsheet of how the data is laid out with all possibilities - so when you apply to real data , it has not changed in anyway

Right, but my problem is that I am not at the level to know how to organize my data better than how I already have it now. It looks the way it does because it was the best I could do at that moment, and I've been using it for a long time. There used to be a blank row under each year, and I would sum the prices of each publisher/supplier as well as totals underneath it. Then there would be an additional blank row under each department grouping. The full table looks the same so I don't think it would make a difference pasting the XL2BB other than making it long to scroll. It would also take too long to anonymize everything if I uploaded it. All the other departments follow the same organization: they start with year 1 and 2 courses, followed by years 3 and 4 courses and their minors. Once I get to the last year 4 course, I start the next row with the first year 1 course of the next department.

So how do I add those two countifs together?? I know how to copy &paste formulas, and edit them a little, but I wouldn't know how to combine the ones I already have. What do I *do* with these?

=IF(F4="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"-",Table1[Year],"1")))

=IF(F7="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"-",Table1[Year],"2")))

=IF(F12="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Year],"3")))

=IF(F16="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"3")))

=IF(F17="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"3")))

=IF(F19="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Year],"4")))

=IF(F22="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"4")))

=IF(F24="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"4")))

=IF(F26="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Economics",Table1[Year],"4")))

and so on and so forth for Table1[Major],"Economics", Table1[Major],"Political Science" etc. once they are all added.

How do I achieve that I only have one formula that would take all of these criteria into account? Is there a better way to organize the table so that it can be understood by lecturers and management when they want to choose the best supplier? How do I pick up the totals for each student package as well as those for year and publisher/supplier totals, create reports, track what's late, incomplete? Is this something that should be done with pivot tables (which, I'd have to learn those as well)? Those are all questions that I will have to ask one by one.

Apologies if I've been snappish; I've been trying to get our devs to make a book order application or a database instead of this for a very long time, but they're overbooked so I have to muddle through it.

 

[etaf]

Right, but my problem is that I am not at the level to know how to organize my data better than how I already have it now.

i understand that , hence why i said

and a incremental approach adding bits and checking they work ok

So how do I add those two countifs together?? I know how to copy &paste formulas, and edit them a little, but I wouldn't know how to combine the ones I already have. What do I *do* with these?

you can just add together
countifs() + countifs()

and also add IF , with a countifs() together

FOR Example
and only an example ....
=IF(F24="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Business",Table1[Year],"4"))) + IF(F26="No textbook",0,(COUNTIFS(Table1[Major],"Computer Science",Table1[Minor],"Economics",Table1[Year],"4")))

NOW
in the last post
i mentioned , again just an example

=IF(F8="",0,(COUNTIFS(Data!$F:$F,"Computer Science",Data!$G:$G,"-",Data!$H:$H,"1")))

so that could be changed to
=IF(F8="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$G:$G,"-",Data!$H:$H,A8)))

so instead of using the words, i replaced with cells , so it could be copied down

thats when i mentioned the - and maybe changing that in the data file
OR
adding together perhaps
=IF(F22="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$G:$G,"-",Data!$H:$H,A8)))
will count all the year and major
so you could add another formula like
=IF(F22="No textbook",0,(COUNTIFS(Table1[Major],"$D$2",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"4")))
so it becomes something like
=IF(F22="",0,(COUNTIFS(Data!$F:$F,$D$2,Data!$G:$G,"-",Data!$H:$H,A8))) + IF(F22="No textbook",0,(COUNTIFS(Table1[Major],"$D$2",Table1[Minor],"Electronic and Electrical Engineering",Table1[Year],"4")))

and again, i replaced "4" with the cell 4 was in that list
with A8 in my example

i'll have a read again with your formulas and see what thye are doing and maybe replace

away at weekend

 

[etaf]

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