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

drom

Well-known Member
Joined
Mar 20, 2005
Messages
521
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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
ABC
3SchoolKids nameSum of Unjustified absences
4XXXXMike0
5Mary0
6Juan3
7Steven3
8Peter8
9Sean9
10XXXX Total23
11Grand Total23
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.
 
Upvote 0
You still haven't provided a data sample...posting a PivotTable result isn't much use if you don't want to use one !
 
Upvote 0
Sorry I thought I was providing one.
Book1
ABCDEFGHI
1STATESchoolKids nameSubjectsUnjustified absencesMarksDATEYEARMONTH
2TexasXXXXJuanHistory222021-mar-01202103
3TexasXXXXJuanChess142021-mar-02202103
4TexasXXXXJuanFrench022021-mar-03202103
5New YorkXXXXMaryChess022021-mar-01202103
6New YorkXXXXMaryHistory052021-mar-02202103
7New YorkXXXXMaryMaths052021-mar-03202103
8New YorkXXXXMikeFrench082021-mar-01202103
9New YorkXXXXMikeMaths052021-mar-02202103
10New YorkXXXXMikeFrench022021-mar-02202103
11New YorkXXXXMikeMaths052021-mar-03202103
12TexasXXXXPeterHistory092021-mar-01202103
13TexasXXXXPeterMaths232021-mar-01202103
14TexasXXXXPeterFrench092021-mar-02202103
15TexasXXXXPeterHistory432021-mar-02202103
16TexasXXXXPeterMaths292021-mar-02202103
17TexasXXXXPeterHistory052021-mar-03202103
18TexasXXXXPeterHistory092021-mar-03202103
19IdahoXXXXSeanHistory052021-mar-01202103
20IdahoXXXXSeanFrench492021-mar-02202103
21IdahoXXXXSeanHistory382021-mar-02202103
22IdahoXXXXSeanFrench292021-mar-03202103
23TexasXXXXStevenMaths082021-mar-02202103
24TexasXXXXStevenHistory122021-mar-02202103
25TexasXXXXStevenChess222021-mar-02202103
26TexasXXXXStevenHistory082021-mar-03202103
DATA
Cell Formulas
RangeFormula
H2:H26H2=YEAR([@DATE])
I2:I26I2=MONTH([@DATE])
F2:F26F2=RANDBETWEEN(2,9)
 
Upvote 0
In say K2 put the formula
Excel Formula:
=UNIQUE($C$2:$C$26)
and drag down
Then in an adjacent column use
Excel Formula:
=SUMPRODUCT(--($C$2:$C$26=K2)*$E$2:$E$26)
and drag down
Kids Nameabsences
Mike0
Mary0
Juan3
Steven3
Peter8
Sean9
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top