Using counta and countif

[Jowej]

Hi, I have a spreadsheet and on one of the sheets named 'data' I have names (column C), areas (column E) and categories from row F1:P1. For easy reference I will call the areas 'class' and categories 'subjects'. It looks something like below

[TABLE="width: 387"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]ART[/TD]
[TD]MATHS[/TD]
[TD]HISTORY[/TD]
[/TR]
[TR]
[TD]NAME 1[/TD]
[TD]CLASS C[/TD]
[TD]15/07/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 2[/TD]
[TD]CLASS A[/TD]
[TD]03/03/2018[/TD]
[TD][/TD]
[TD]26/01/2018[/TD]
[/TR]
[TR]
[TD]NAME 3[/TD]
[TD]CLASS C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 4[/TD]
[TD]CLASS B[/TD]
[TD]28/02/2018[/TD]
[TD]16/05/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 5[/TD]
[TD]CLASS B[/TD]
[TD][/TD]
[TD]04/12/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 6[/TD]
[TD]CLASS A[/TD]
[TD]22/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 7[/TD]
[TD]CLASS C[/TD]
[TD]14/11/2018[/TD]
[TD]17/06/2018[/TD]
[TD]04/10/2018[/TD]
[/TR]
[TR]
[TD]NAME 8[/TD]
[TD]CLASS A[/TD]
[TD][/TD]
[TD]23/08/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 9[/TD]
[TD]CLASS A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NAME 10[/TD]
[TD]CLASS A[/TD]
[TD][/TD]
[TD][/TD]
[TD]16/10/2018[/TD]
[/TR]
</tbody>[/TABLE]


In another sheet I am looking up data from this sheet. In cell D2 I have a validation looking at the all the subjects in row 1 from F:P. In C4 I have 'Class A', in D4 'Class B' etc. In C5 I have a basic counta formula counting the total numbers for Class A etc . The formula I need is for cell C6 which will be look at cell C4 (Class A) and cell D2 (the subject) against my data and return the number for that, in this case if we chose Class A and Art it would return 2. The data would change as I changed my subject.

In addition to this I also want to list the names of anyone in Class A and Art that does not have a date (this would be in cell C12. Class B would be in D12. In this instance it would bring off Name 2, Name 8 and Name 10 and again that would change as cell D2 (subject) changed

I hope this makes sense and any help is appreciated. I have tried countif by using another sheet and converting the dates to numbers and also sum product

 

[RasGhul]

Hi Jowej,

I converted your original table into a data map on another sheet, with the output on Sheet 3. Some of the formulas you will need to drag across first.

Book1
	C	D	E	F	G	H
1	Students		Areas	ART	MATHS	HISTORY
2	NAME 1		CLASS C	15-07-18
3	NAME 2		CLASS A	03-03-18		26-01-18
4	NAME 3		CLASS C
5	NAME 4		CLASS B	28-02-18	16-05-18
6	NAME 5		CLASS B		04-12-18
7	NAME 6		CLASS A	22-08-18
8	NAME 7		CLASS C	14-11-18	17-06-18	04-10-18
9	NAME 8		CLASS A		23-08-18
10	NAME 9		CLASS A
11	NAME 10		CLASS A			16-10-18
Data

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr>

Book1
	A	B	C	D	E
1			ART	MATHS	HISTORY
2			3	4	5
3	NAME 1	CLASS C	ART	4	5
4	NAME 2	CLASS A	ART	4	HISTORY
5	NAME 3	CLASS C	3	4	5
6	NAME 4	CLASS B	ART	MATHS	5
7	NAME 5	CLASS B	3	MATHS	5
8	NAME 6	CLASS A	ART	4	5
9	NAME 7	CLASS C	ART	MATHS	HISTORY
10	NAME 8	CLASS A	3	MATHS	5
11	NAME 9	CLASS A	3	4	5
12	NAME 10	CLASS A	3	4	HISTORY
Map

Cell Formulas
Range	Formula
C2	=COLUMN()
C3	=IF(ISNUMBER(Data!F2),C$1,COLUMN())
A3	=Data!C2
B3	=Data!E2

Book1
	B	C	D	E
1			SUBJECT	Array
2			ART	3
3
4		Class A	Class B	Class C
5	Counts	5	2	3
6	vs Subject	2	1	2
7
8
9
10
11
12	Non-Enrolled	NAME 8	NAME 5	NAME 3
13		NAME 9
14		NAME 10
Sheet3

Cell Formulas
Range	Formula
E2	=INDEX(Map!$C$2:$E$2,MATCH(Sheet3!$D$2,Map!$C$1:$E$1,0))
C6	=SUMPRODUCT((Map!$C$3:$E$12=Sheet3!$D$2)*(Map!$B$3:$B$12=Sheet3!C$4))
C12	{=IFERROR(INDEX(Map!$A$3:$A$12,SMALL(IF(Map!$B$3:$B$12=Sheet3!C$4,IF(Map!$C$3:$E$12=Sheet3!$E$2,ROW(Map!C$3:C$12)-ROW(Map!C$3)+1)),ROWS(Sheet3!C$12:C12))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.