fine on a 2nd sheet - just post xl2bb or share the file
countifs() - using more than 1 criteria
so you can
=countifs( range of year , year , range of major , major) + countifs( range of year , year , range of minor , minor)
Alright, let me see if I can explain what I am trying to do, and then I can anonymize the data and share it.
For each department in a given semester I get a list of students, courses and textbooks they will use for those courses. I need to order enough textbooks to cover all students. Some textbooks are ordered directly through the publisher, for others I need to receive price quotes from suppliers. This information needs to be separated into Publisher 1, Publisher 2, and Quote sheets. I've got that part solved.
The shared table shows only CS department years 1 through 4, but the next department (Economics) would be listed starting with Year 1 right below the CS Year 4. After that I would have Political Science and on, until I had all the departments listed. This is because I need to know the price of the single student package by department and year, and the total price of all copies in a year and department.
The quantities for each course currently need to be entered manually by checking how many students are attending which common courses and minor courses. I would like formulas to do the following:
- if there is no textbook in column F, fill the cell in column J with 0 (I've got that part solved)
- if there is no lecturer in column E, fill the cell in column K with 0 (I've got that part solved)
- if there is a textbook in column F, fill in the necessary quantity in column J based on the major, minor, and year columns in the data sheet (years 1 and 2 have no minors, while years 3 and 4 do)
- if there is a lecturer in column E, add an additional copy in cell K (I've got that part solved)
- in the TO ORDER column M add quantity and instructor copies, and subtract in stock copies (I've got that part solved)
When it comes to prices, I already have these formulas:
- MAX formula to get the Price final recorded in the direct order columns
- INDEX and MATCH formulas to get the Supplier final based on the Price final and direct order vs local supplier named ranges
- IF and ISBLANK formulas which determine where a UNIQUE filter will separate the information: in two publisher sheets or the quote needed sheet.
The problem is that if there's no textbook, IF and ISBLANK still records that as quote needed, while the quantity is zero. I suppose I could copy & paste that information as values and manually delete the zero quantity lines, but is there a way for the formula to recognize that 0 textbook also means 0 quote needed?
I basically want a workbook where the only information I will need to enter manually would be the quantities we already have in stock, and the prices we receive from the publishers and suppliers. Everything else I want the formulas to pick up from already existing data - list of all students grouped by year, major, minor; list of courses and their lecturers and textbooks (this part I have no idea how to do; I have a vague idea that it may be done with pivot tables, which I know nothing about).
| ORDER CALCULATIONS ANONYMIZED.xlsx |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y |
|---|
| 5 | | | | | | | | | | | | | | FOREIGN | SUPPLIERS | LOCAL | SUPPLIERS | | | | | | | | |
|---|
| 6 | | | | | | | | | | | | | | | DIRECT | ORDERS | | | | | | | | | |
|---|
| 7 | | Year | Code | Title | Lecturer | Textbook | Author | Publisher | ISBN | Quantity | Instructor | In stock | TO ORDER | CENGAGE | PEARSON | SUPPLIER1 | SUPPLIER2 | SUPPLIER3 | QUOTE | Supplier Final | Price final | Availability | Tracking | COST | ARRIVED # |
|---|
| 8 | | 1 | CS121 | Computer Organization and Architecture | Name Surname | Computer Organization and Architecture | | McGraw-Hill | | 5 | 1 | 0 | 6 | | | | $ 40.00 | $ 50.00 | 1 | SUPPLIER2 | $ 40.00 | | Pending | $ 240.00 | |
|---|
| 9 | | 1 | CS122 | Programming and Problem Solving II | Name Surname | Programming and Problem Solving II | | Pearson | | 5 | 1 | 17 | 0 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ - | |
|---|
| 10 | | 1 | Math123 | Discrete Mathematics | Name Surname | Discrete Mathematics | | Pearson | | 5 | 1 | 16 | 0 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ - | |
|---|
| 11 | | 2 | CS221 | Database Systems | Name Surname | Database Systems | | CENGAGE | | 5 | 1 | 0 | 6 | $ 50.00 | | | | | 0 | CENGAGE | $ 50.00 | | Pending | $ 300.00 | |
|---|
| 12 | | 2 | CS222 | Operating Systems | Name Surname | Operating Systems | | Wiley | | 5 | 1 | 0 | 6 | | | | $ 50.00 | $ 40.00 | 1 | SUPPLIER3 | $ 40.00 | | Pending | $ 240.00 | |
|---|
| 13 | | 2 | CS223 | Cloud Computing | Name Surname | Cloud Computing | | Pearson | | 5 | 1 | 0 | 6 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 300.00 | |
|---|
| 14 | | 2 | CS224 | Design and Implementation in Web Environments | Name Surname | Design and Implementation in Web Environments | | Pearson | | 5 | 1 | 0 | 6 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 300.00 | |
|---|
| 15 | | 2 | CS225 | Design and Implementation in Web Environments | Name Surname | Design and Implementation in Web Environments | | Pearson | | 5 | 1 | 0 | 6 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 300.00 | |
|---|
| 16 | Common courses | 3 | CS321 | Project management | Name Surname | Project management | | Project Management Institute | | 5 | 1 | 0 | 6 | | | | $ 40.00 | $ 50.00 | 1 | SUPPLIER2 | $ 40.00 | | Pending | $ 240.00 | |
|---|
| 17 | | 3 | CS322 | Business Intelligence | Name Surname | Business Intelligence | | Pearson | | 5 | 1 | 17 | 0 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ - | |
|---|
| 18 | | 3 | CS323 | Software Engineering | Name Surname | Software Engineering | | Pearson | | 5 | 1 | 16 | 0 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ - | |
|---|
| 19 | | 3 | ENG324 | Reports and Professional Correspondence | Name Surname | | | | | 0 | 0 | 0 | 0 | | | | | | 1 | #N/A | $ - | | Pending | $ - | |
|---|
| 20 | Electrical Engineering | 3 | EE325 | Electrical Engineering | | Electrical Engineering | | Wiley – IEEE Press | | 5 | 0 | 0 | 5 | | | | $ 50.00 | $ 40.00 | 1 | SUPPLIER3 | $ 40.00 | | Pending | $ 200.00 | |
|---|
| 21 | Business | 3 | EC326 | Marketing fundamentals | | | | | | 0 | 0 | 0 | 0 | | | | | | 1 | #N/A | $ - | | Pending | $ - | |
|---|
| 22 | Economics | 3 | EC327 | Introduction to Macroecenomics | Name Surname | Introduction to Macroecenomics | | Pearson | | 3 | 1 | 0 | 4 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 200.00 | |
|---|
| 23 | Common courses | 4 | CS421 | Applied Programming | Name Surname | Applied Programming | | Pearson | | 5 | 1 | 0 | 6 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 300.00 | |
|---|
| 24 | | 4 | CS422 | Embedded Systems | Name Surname | Embedded Systems | | Springer | | 5 | 1 | 17 | 0 | | | | $ 40.00 | $ 50.00 | 1 | SUPPLIER2 | $ 40.00 | | Pending | $ - | |
|---|
| 25 | | 4 | CS423 | Dissertation Research and Writing | Name Surname | Dissertation Research and Writing | | Red Globe Press / Macmillan | | 5 | 1 | 16 | 0 | | | | $ 50.00 | $ 40.00 | 1 | SUPPLIER3 | $ 40.00 | | Pending | $ - | |
|---|
| 26 | Electrical Engineering | 4 | EE425 | Multimedia Electronics | Name Surname | Multimedia Electronics | | Pearson | | 2 | 1 | 0 | 3 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 150.00 | |
|---|
| 27 | | 4 | EE426 | Selected topics in engineering | Name Surname | Selected topics in engineering | | Wiley – IEEE Press | | 2 | 1 | 0 | 3 | | | | $ 40.00 | $ 50.00 | 1 | SUPPLIER2 | $ 40.00 | | Pending | $ 120.00 | |
|---|
| 28 | Business | 4 | EC427 | Corporate Strategy and Strategic Management | Name Surname | Corporate Strategy and Strategic Management | | CENGAGE | | 2 | 1 | 0 | 3 | $ 50.00 | | | | | 0 | CENGAGE | $ 50.00 | | Pending | $ 150.00 | |
|---|
| 29 | | 4 | EC428 | Introduction to finance | Name Surname | Introduction to finance | | Pearson | | 2 | 1 | 0 | 3 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 150.00 | |
|---|
| 30 | Economics | 4 | EC429 | Corporate Social Responsibility | Name Surname | Corporate Social Responsibility | | Pearson | | 1 | 1 | 0 | 2 | | $ 50.00 | | | | 0 | PEARSON | $ 50.00 | | Pending | $ 100.00 | |
|---|
|
|---|
