AusSteelMan
Board Regular
- Joined
- Sep 4, 2009
- Messages
- 208
Hi everyone,
Firstly, thanks for reading my question.
I have an issue where I would like to count the number of classes per subject that have occurred since a date.
This table shows the subjects that I would like to count:
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"></colgroup><tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
This table shows each subject, the last date (i.e. I want to count each subject that falls after this date), the column where I have been trying to calculate it and finally the results that I think I should have (colour coded from the first table just to make it easier to see. it serves no other purpose)
Sheet1
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:136px;"><col style="width:252px;"><col style="width:134px;"></colgroup><tbody>
</tbody>
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I will note that in the real workbook:
Can anybody please help out with fixing the formula so it works?
I have tried the first part of the formula (counting all occurrences of each subject) by itself and it works OK.
I have tried a few variants of the date portion but just can't get it.
I have tried using on a fixed date without any greater than or greater than equal to where I know a subject exists (so should get a count of 1) but still doesn't work at all.
I have searched this site and others but just can't find anything that works
If I need to use helper columns etc, that is OK. For a number of reasons I'd prefer to avoid any VBA.
The "Never" "Last Date" can easily be replaced with a real date (such as 28/1/2016)
Many thanks in advance.
Cheers,
Darren
Firstly, thanks for reading my question.
I have an issue where I would like to count the number of classes per subject that have occurred since a date.
This table shows the subjects that I would like to count:
Sheet1
* | A | B | C | D | E | F | G |
1 | Date | Missed classes_P1 | Missed classes_P2 | Missed classes_P3 | Missed classes_P4 | Missed classes_P5 | Missed classes_P6 |
2 | 29/01/2016 | * | * | * | * | * | * |
3 | 1/02/2016 | * | * | * | * | English | Maths |
4 | 2/02/2016 | * | * | * | * | Inv Maths | Graphics |
5 | 3/02/2016 | * | * | * | * | HSIE | Graphics |
6 | 4/02/2016 | PDH | English | Science | HSIE | Maths | CAT |
7 | 5/02/2016 | Maths | Inv Maths | Graphics | Graphics | CAT | English |
8 | 8/02/2016 | * | * | * | * | Science | English |
9 | 9/02/2016 | Maths | Inv Maths | Science | English | Graphics | HSIE |
10 | 10/02/2016 | English | Maths | PDH | HSIE | Science | CAT |
11 | 11/02/2016 | Science | Maths | English | CAT | Sport | Sport |
12 | 12/02/2016 | * | English | Inv Maths | Science | Graphics | HSIE |
13 | 15/02/2016 | * | * | * | * | English | Maths |
14 | 16/02/2016 | * | * | * | * | Inv Maths | Graphics |
15 | 17/02/2016 | * | * | * | Science | HSIE | Graphics |
16 | 18/02/2016 | PDH | English | Science | HSIE | Maths | CAT |
17 | 19/02/2016 | * | * | * | * | CAT | English |
18 | 22/02/2016 | * | * | * | * | Science | English |
19 | 23/02/2016 | Maths | Inv Maths | Science | English | Graphics | HSIE |
20 | 24/02/2016 | English | Maths | PDH | HSIE | Science | CAT |
21 | 25/02/2016 | Science | Maths | English | CAT | Sport | Sport |
22 | 26/02/2016 | Maths | English | Inv Maths | Science | Graphics | HSIE |
23 | 1/03/2016 | Maths | HSIE | Sport | * | Inv Maths | Graphics |
24 | 2/03/2016 | Maths | English | Sport | * | * | * |
25 | 3/03/2016 | PDH | English | Science | * | * | * |
26 | 4/03/2016 | Maths | Inv Maths | Graphics | Graphics | CAT | English |
27 | 7/03/2016 | Graphics | HSIE | Inv Maths | Inv Maths | Science | English |
28 | 8/03/2016 | Maths | Inv Maths | Science | English | Graphics | HSIE |
29 | 9/03/2016 | English | Maths | PDH | HSIE | Science | CAT |
30 | 10/03/2016 | Science | Maths | English | CAT | Sport | Sport |
31 | 11/03/2016 | Maths | English | Inv Maths | Science | Graphics | HSIE |
32 | 14/03/2016 | Science | HSIE | Inv Maths | CAT | English | Maths |
33 | 15/03/2016 | Maths | HSIE | Sport | Science | Inv Maths | Graphics |
34 | 16/03/2016 | Maths | English | Sport | Science | HSIE | Graphics |
35 | 17/03/2016 | PDH | English | Science | HSIE | Maths | CAT |
36 | 18/03/2016 | Maths | Inv Maths | Graphics | Graphics | CAT | English |
37 | 21/03/2016 | Graphics | HSIE | Inv Maths | Inv Maths | * | * |
38 | 22/03/2016 | * | * | * | * | * | * |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"><col style="width:137px;"></colgroup><tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
This table shows each subject, the last date (i.e. I want to count each subject that falls after this date), the column where I have been trying to calculate it and finally the results that I think I should have (colour coded from the first table just to make it easier to see. it serves no other purpose)
Sheet1
* | I | J | K | L |
1 | Subject | Last Date | Lessons missed since last attendance | Answer should be |
2 | Maths | 3/03/2016 | #VALUE! | 10 |
3 | English | 21/03/2016 | #VALUE! | 0 |
4 | Science | 21/03/2016 | #VALUE! | 0 |
5 | Inv Maths | 22/02/2016 | #VALUE! | 13 |
6 | Graphics | 2/03/2016 | #VALUE! | 10 |
7 | HSIE | 3/03/2016 | #VALUE! | 9 |
8 | CAT | 3/03/2016 | #VALUE! | 6 |
9 | PDH | Never | #VALUE! | 7 |
10 | Sport | 17/02/2016 | #VALUE! | 8 |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:136px;"><col style="width:252px;"><col style="width:134px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas | ||||
<tbody> </tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I will note that in the real workbook:
- the "Last date" is a calculated answer
- the "Missed classes_Pn" are calculated
- Both tables are Excel Tables not just pretty formatting
Can anybody please help out with fixing the formula so it works?
I have tried the first part of the formula (counting all occurrences of each subject) by itself and it works OK.
I have tried a few variants of the date portion but just can't get it.
I have tried using on a fixed date without any greater than or greater than equal to where I know a subject exists (so should get a count of 1) but still doesn't work at all.
I have searched this site and others but just can't find anything that works
If I need to use helper columns etc, that is OK. For a number of reasons I'd prefer to avoid any VBA.
The "Never" "Last Date" can easily be replaced with a real date (such as 28/1/2016)
Many thanks in advance.
Cheers,
Darren