Count number of kids with less than X Absences in ...

[drom]

Hi and thanks in advance

I need a formula (I know how to do this using VBA, I use: for each and/or Loopings)

• If is faster with a formula via VBA PERFECT
• Or using a Formula in a sheet, not using VBA, Perfect as well

I have a table named Table1
This table has many Dinamic rows (today 1000, tomorrow maeby 1250, day after who knows?

I have X columns which headers are:

• A1 = State
• B1 = School
• C1 = Kids name
• D1 = Subjects
• E1 = Unjustified absences
• F1 = Marks
• G1 = DATE
• H1 = YEAR
• I1 = MONTH

Si If a have 20 school days in a month In a school named XXXX and

• A kid named Peter stays out of the school maeby monday he will have probably 5/6 Unjustified absences
• Mary and Mike (good students ) if they go to class always

I would like to have a formula to know how many kids:

• Do I have in a this School (XXXX) with less than 5 absences a month (say MARCH) (At least 2, Mary and Mike)
• And How many with kids with absences between 5 and 10 ( At least 1 Peter)

But the formula to get this ?

Thanks again!

 

[Michael M]

A bit hard without data to test.
Can you use the XL2BB to paste a block of data with expected results ?
You can download the Xl2BB addin either in my Sig block or it is available in the reply toolbar.

 

[drom]

History

And with a pivot table:

• I get , I can see, I have 2 kids with absences between 5 and 10 (see gray color)
• And 4 with less than 5 absences (see yellow color)

but I do not want to use a Pivot table

Book1
	A	B	C
3	School	Kids name	Sum of Unjustified absences
4	XXXX	Mike	0
5		Mary	0
6		Juan	3
7		Steven	3
8		Peter	8
9		Sean	9
10	XXXX Total		23
11	Grand Total		23
PT

A bit hard without data to test.
Can you use the XL2BB to paste a block of data with expected results ?
You can download the Xl2BB addin either in my Sig block or it is available in the reply toolbar.

 

[Michael M]

You still haven't provided a data sample...posting a PivotTable result isn't much use if you don't want to use one !

 

[drom]

Sorry I thought I was providing one.

Book1
	A	B	C	D	E	F	G	H	I
1	STATE	School	Kids name	Subjects	Unjustified absences	Marks	DATE	YEAR	MONTH
2	Texas	XXXX	Juan	History	2	2	2021-mar-01	2021	03
3	Texas	XXXX	Juan	Chess	1	4	2021-mar-02	2021	03
4	Texas	XXXX	Juan	French	0	2	2021-mar-03	2021	03
5	New York	XXXX	Mary	Chess	0	2	2021-mar-01	2021	03
6	New York	XXXX	Mary	History	0	5	2021-mar-02	2021	03
7	New York	XXXX	Mary	Maths	0	5	2021-mar-03	2021	03
8	New York	XXXX	Mike	French	0	8	2021-mar-01	2021	03
9	New York	XXXX	Mike	Maths	0	5	2021-mar-02	2021	03
10	New York	XXXX	Mike	French	0	2	2021-mar-02	2021	03
11	New York	XXXX	Mike	Maths	0	5	2021-mar-03	2021	03
12	Texas	XXXX	Peter	History	0	9	2021-mar-01	2021	03
13	Texas	XXXX	Peter	Maths	2	3	2021-mar-01	2021	03
14	Texas	XXXX	Peter	French	0	9	2021-mar-02	2021	03
15	Texas	XXXX	Peter	History	4	3	2021-mar-02	2021	03
16	Texas	XXXX	Peter	Maths	2	9	2021-mar-02	2021	03
17	Texas	XXXX	Peter	History	0	5	2021-mar-03	2021	03
18	Texas	XXXX	Peter	History	0	9	2021-mar-03	2021	03
19	Idaho	XXXX	Sean	History	0	5	2021-mar-01	2021	03
20	Idaho	XXXX	Sean	French	4	9	2021-mar-02	2021	03
21	Idaho	XXXX	Sean	History	3	8	2021-mar-02	2021	03
22	Idaho	XXXX	Sean	French	2	9	2021-mar-03	2021	03
23	Texas	XXXX	Steven	Maths	0	8	2021-mar-02	2021	03
24	Texas	XXXX	Steven	History	1	2	2021-mar-02	2021	03
25	Texas	XXXX	Steven	Chess	2	2	2021-mar-02	2021	03
26	Texas	XXXX	Steven	History	0	8	2021-mar-03	2021	03
DATA

Cell Formulas
Range	Formula
H2:H26	H2	=YEAR([@DATE])
I2:I26	I2	=MONTH([@DATE])
F2:F26	F2	=RANDBETWEEN(2,9)

 

[Michael M]

In say K2 put the formula

=UNIQUE($C$2:$C$26)

and drag down
Then in an adjacent column use

=SUMPRODUCT(--($C$2:$C$26=K2)*$E$2:$E$26)

and drag down

Kids Name	absences
Mike	0
Mary	0
Juan	3
Steven	3
Peter	8
Sean	9