Countifs with a regular criteria and a criteria with a date

[AusSteelMan]

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

*	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

Cell Formula K2 =COUNTIFS(Table1[[Missed classes_P1]:[Missed classes_P6]],[@Subject],Table1[Date],">="&[@[Last Date]]) <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

 

[AusSteelMan]

So I have come up with a helper column "solution" that really is fairly ugly but seems to work.

Sheet1

*	A	B	C	D	E	F	G	H	I	J	K
1	Date	Missed classes_P1	Missed classes_P2	Missed classes_P3	Missed classes_P4	Missed classes_P5	Missed classes_P6	Maths	English	Graphics	PDH
2	29/01/2016	*	*	*	*	*	*	0	0	0	0
3	1/02/2016	*	*	*	*	English	Maths	0	0	0	0
4	2/02/2016	*	*	*	*	Inv Math	Graphics	0	0	0	0
5	3/02/2016	*	*	*	*	HSIE	Graphics	0	0	0	0
6	4/02/2016	PDH	English	Science	HSIE	Maths	CAT	0	0	0	1
7	5/02/2016	Maths	Inv Math	Graphics	Graphics	CAT	English	0	0	0	0
8	8/02/2016	*	*	*	*	Science	English	0	0	0	0
9	9/02/2016	Maths	Inv Math	Science	English	Graphics	HSIE	0	0	0	0
10	10/02/2016	English	Maths	PDH	HSIE	Science	CAT	0	0	0	1
11	11/02/2016	Science	Maths	English	CAT	Sport	Sport	0	0	0	0
12	12/02/2016	*	English	Inv Math	Science	Graphics	HSIE	0	0	0	0
13	15/02/2016	*	*	*	*	English	Maths	0	0	0	0
14	16/02/2016	*	*	*	*	Inv Math	Graphics	0	0	0	0
15	17/02/2016	*	*	*	Science	HSIE	Graphics	0	0	0	0
16	18/02/2016	PDH	English	Science	HSIE	Maths	CAT	0	0	0	1
17	19/02/2016	*	*	*	*	CAT	English	0	0	0	0
18	22/02/2016	*	*	*	*	Science	English	0	0	0	0
19	23/02/2016	Maths	Inv Math	Science	English	Graphics	HSIE	0	0	0	0
20	24/02/2016	English	Maths	PDH	HSIE	Science	CAT	0	0	0	1
21	25/02/2016	Science	Maths	English	CAT	Sport	Sport	0	0	0	0
22	26/02/2016	Maths	English	Inv Math	Science	Graphics	HSIE	0	0	0	0
23	1/03/2016	Maths	HSIE	Sport	*	Inv Math	Graphics	0	0	0	0
24	2/03/2016	Maths	English	Sport	*	*	*	0	0	0	0
25	3/03/2016	PDH	English	Science	*	*	*	0	0	0	1
26	4/03/2016	Maths	Inv Math	Graphics	Graphics	CAT	English	1	0	2	0
27	7/03/2016	Graphics	HSIE	Inv Math	Inv Math	Science	English	0	0	1	0
28	8/03/2016	Maths	Inv Math	Science	English	Graphics	HSIE	1	0	1	0
29	9/03/2016	English	Maths	PDH	HSIE	Science	CAT	1	0	0	1
30	10/03/2016	Science	Maths	English	CAT	Sport	Sport	1	0	0	0
31	11/03/2016	Maths	English	Inv Math	Science	Graphics	HSIE	1	0	1	0
32	14/03/2016	Science	HSIE	Inv Math	CAT	English	Maths	1	0	0	0
33	15/03/2016	Maths	HSIE	Sport	Science	Inv Math	Graphics	1	0	1	0
34	16/03/2016	Maths	English	Sport	Science	HSIE	Graphics	1	0	1	0
35	17/03/2016	PDH	English	Science	HSIE	Maths	CAT	1	0	0	1
36	18/03/2016	Maths	Inv Math	Graphics	Graphics	CAT	English	1	0	2	0
37	21/03/2016	Graphics	HSIE	Inv Math	Inv Math	*	*	0	0	1	0
38	22/03/2016	*	*	*	*	*	*	0	0	0	0
39	Total	*	*	*	*	*	*	10	0	10	7

<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;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula H26 =IF([@Date]>INDEX(Table2[Last Date],MATCH(Table1[[#Headers],[Maths]],Table2[Subject],0)),COUNTIF(Table1[@[Missed classes_P1]:[Missed classes_P6]],Table1[[#Headers],[Maths]]),0) I26 =IF([@Date]>INDEX(Table2[Last Date],MATCH(Table1[[#Headers],[English]],Table2[Subject],0)),COUNTIF(Table1[@[Missed classes_P1]:[Missed classes_P6]],Table1[[#Headers],[English]]),0) J26 =IF([@Date]>INDEX(Table2[Last Date],MATCH(Table1[[#Headers],[Graphics]],Table2[Subject],0)),COUNTIF(Table1[@[Missed classes_P1]:[Missed classes_P6]],Table1[[#Headers],[Graphics]]),0) K26 =IF([@Date]>INDEX(Table2[Last Date],MATCH(Table1[[#Headers],[PDH]],Table2[Subject],0)),COUNTIF(Table1[@[Missed classes_P1]:[Missed classes_P6]],Table1[[#Headers],[PDH]]),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Each helper column (only 4 at this stage) counts that subject (column name) if the date if after the "Last Date" in the second table.


Sheet1

*	M	N	O	P	Q
1	Subject	Last Date	Lessons missed since last attendance	Answer should be	Totals (Index/Match)
2	Maths	3/03/2016	10	10	10
3	English	21/03/2016	0	0	0
4	Science	21/03/2016	*	0	#N/A
5	Inv Math	22/02/2016	*	13	#N/A
6	Graphics	2/03/2016	10	10	10
7	HSIE	3/03/2016	*	9	#N/A
8	CAT	3/03/2016	*	6	#N/A
9	PDH	28/01/2016	*	7	7
10	Sport	17/02/2016	*	8	#N/A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:136px;"><col style="width:252px;"><col style="width:134px;"><col style="width:147px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula O2 =SUM(Table1[Maths]) Q2 =INDEX(Table1[#Totals],MATCH([@Subject],Table1[#Headers],0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Then the desired answer is a sum of each helper column. Then in the extra column I used an Index/Match after turning on the Totals row on the first table to "lookup" the result. this extra step allows each row formula to be the same, whereas the other requires each cell to have its own formula (defeating a primary aim of an Excel Table)

Like I say, I don't like it much since it uses many extra columns and calculations to do something that seems logical that SUMIFS should be able to do.

Anybody got any comments and/or suggestion/fixes?

Cheers,
Darren

 

[AusSteelMan]

Can any legends out there help me out please?

I have been searching again but no luck. i see there were a few other countif questions since my last post, but I don't see how I can apply any of those specific answers.


A question though:
Have i been clear enough in my question to date? Is there any other info I could provide that may help?

I have a table with a date reference followed by the 6 classes columns.
I need to count the number of times a subject appears in the classes column but only after the corresponding date for each subject.

Many thanks,
Darren

 

[MARZIOTULLIO]

	I​	J​	K​	L​
1​	Subject	Last Date	Lessons missed since last attendance	Answer should be
2​	Maths	3/3/2016​	10​	10​
3​	English	3/21/2016​	0​	0​
4​	Science	3/21/2016​	0​	0​
5​	Inv Maths	2/22/2016​	13​	13​
6​	Graphics	3/2/2016​	10​	10​
7​	HSIE	3/3/2016​	9​	9​
8​	CAT	3/3/2016​	6​	6​
9​	PDH		7​	7​
10​	Sport	2/17/2016​	8​	8​
11​

<tbody>
</tbody>

K2=SUMPRODUCT((TABLE1[[Missed classes_P1]:[Missed classes_P6]]=[@Subject])*(TABLE1[Date]>[@[Last Date]])) copy down


PDH EMPTY ERASE "NEVER"

 

[AusSteelMan]

So I came up with another "not-so-friendly" way. This involved customising the COUNTIF range based on the required date. This needed ADDRESS and INDIRECT. Works great, just not as clean as MARZIOTULLIO's SUMPRODUCT method that I found here when I came to post my way.

So I added this method too, as shown below

Sheet1

*	M	N	O	P	Q
1	Subject	Last Date	Lessons missed since last attendance	Answer should be	MARZIOTULLIO's method
2	Maths	3/03/2016	10	10	10
3	English	21/03/2016	0	0	0
4	Science	21/03/2016	0	0	0
5	Inv Math	22/02/2016	13	13	13
6	Graphics	2/03/2016	10	10	10
7	HSIE	3/03/2016	9	9	9
8	CAT	3/03/2016	6	6	6
9	PDH	29/01/2016	7	7	7
10	Sport	17/02/2016	8	8	8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:136px;"><col style="width:344px;"><col style="width:134px;"><col style="width:237px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula O2 =COUNTIF(INDIRECT(ADDRESS(MATCH([@[Last Date]],Table1[Date],0)+1,2)&":"&ADDRESS(MATCH(MAX(Table1[Date]),Table1[Date],0)+1,7)),[@Subject]) Q2 =SUMPRODUCT((Table1[[Missed classes_P1]:[Missed classes_P6]]=[@Subject])*(Table1[Date]>[@[Last Date]])) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



I had tried a SUMPRODUCT at an early stage but I haven't used them much before and couldn't get it to work.

So big thanks to MARZIOTULLIO for your response.

Regards to all,
Darren