Countifs with a regular criteria and a criteria with a date

A

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

*ABCDEFG
Missed classes_P1Missed classes_P2Missed classes_P3Missed classes_P4Missed classes_P5Missed classes_P6
******
****EnglishMaths
****Inv MathsGraphics
****HSIEGraphics
EnglishScienceHSIEMathsCAT
MathsInv MathsGraphicsGraphicsCATEnglish
****ScienceEnglish
MathsInv MathsScienceEnglishGraphicsHSIE
EnglishMathsHSIEScienceCAT
ScienceMathsEnglishCATSportSport
*EnglishInv MathsScienceGraphicsHSIE
****EnglishMaths
****Inv MathsGraphics
***ScienceHSIEGraphics
EnglishScienceHSIEMathsCAT
****CATEnglish
****ScienceEnglish
MathsScienceEnglishGraphicsHSIE
EnglishMathsHSIEScienceCAT
ScienceMathsEnglishCAT
MathsEnglishScienceGraphicsHSIE
MathsHSIE*Graphics
MathsEnglish***
EnglishScience***
English
ScienceEnglish
ScienceEnglish
EnglishScience
ScienceEnglish
EnglishScience
ScienceEnglish
Science
EnglishScience
EnglishScience
English
**
******

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #666699, align: center"]Date[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #dce6f1, align: right"]29/01/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #dce6f1, align: right"]2/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #dce6f1, align: right"]4/02/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]5/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #dce6f1, align: right"]8/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]9/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #dce6f1, align: right"]10/02/2016[/TD]

[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]11/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #dce6f1, align: right"]12/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]15/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #dce6f1, align: right"]16/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]17/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #dce6f1, align: right"]18/02/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]19/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="bgcolor: #dce6f1, align: right"]22/02/2016[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]23/02/2016[/TD]

[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="bgcolor: #dce6f1, align: right"]24/02/2016[/TD]

[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]25/02/2016[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="bgcolor: #dce6f1, align: right"]26/02/2016[/TD]

[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #dce6f1, align: right"]1/03/2016[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="bgcolor: #dce6f1, align: right"]2/03/2016[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="bgcolor: #dce6f1, align: right"]3/03/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="bgcolor: #dce6f1, align: right"]4/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="bgcolor: #dce6f1, align: right"]7/03/2016[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="bgcolor: #dce6f1, align: right"]8/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="bgcolor: #dce6f1, align: right"]9/03/2016[/TD]

[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]

[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="bgcolor: #dce6f1, align: right"]10/03/2016[/TD]

[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #c0c0c0"]CAT[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="bgcolor: #dce6f1, align: right"]11/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="bgcolor: #dce6f1, align: right"]14/03/2016[/TD]

[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="bgcolor: #dce6f1, align: right"]15/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #ffcc00"]Inv Maths[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="bgcolor: #dce6f1, align: right"]16/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="bgcolor: #dce6f1, align: right"]17/03/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="bgcolor: #dce6f1, align: right"]18/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="bgcolor: #dce6f1, align: right"]21/03/2016[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Maths[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="bgcolor: #dce6f1, align: right"]22/03/2016[/TD]

</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

*IJKL
SubjectLast DateLessons missed since last attendanceAnswer should be
Maths#VALUE!
English#VALUE!
Science#VALUE!
Inv Maths#VALUE!
Graphics#VALUE!
HSIE#VALUE!
CAT#VALUE!
PDHNever#VALUE!
Sport#VALUE!

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3/03/2016[/TD]

[TD="bgcolor: #ffff00, align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]21/03/2016[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]21/03/2016[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]22/02/2016[/TD]

[TD="bgcolor: #ffcc00, align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2/03/2016[/TD]

[TD="bgcolor: #99cc00, align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3/03/2016[/TD]

[TD="bgcolor: #969696, align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]3/03/2016[/TD]

[TD="bgcolor: #c0c0c0, align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #00ccff, align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]17/02/2016[/TD]

[TD="bgcolor: #c0c0c0, align: right"]8[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
So I have come up with a helper column "solution" that really is fairly ugly but seems to work.

Sheet1

*ABCDEFGHIJK
Missed classes_P1Missed classes_P2Missed classes_P3Missed classes_P4Missed classes_P5Missed classes_P6MathsEnglishGraphicsPDH
******
****EnglishMaths
****Inv MathGraphics
****HSIEGraphics
EnglishScienceHSIEMathsCAT
MathsInv MathGraphicsGraphicsCATEnglish
****ScienceEnglish
MathsInv MathScienceEnglishGraphicsHSIE
EnglishMathsHSIEScienceCAT
ScienceMathsEnglishCATSportSport
*EnglishInv MathScienceGraphicsHSIE
****EnglishMaths
****Inv MathGraphics
***ScienceHSIEGraphics
EnglishScienceHSIEMathsCAT
****CATEnglish
****ScienceEnglish
MathsScienceEnglishGraphicsHSIE
EnglishMathsHSIEScienceCAT
ScienceMathsEnglishCAT
MathsEnglishScienceGraphicsHSIE
MathsHSIE*Graphics
MathsEnglish***
EnglishScience***
English
ScienceEnglish
ScienceEnglish
EnglishScience
ScienceEnglish
EnglishScience
ScienceEnglish
Science
EnglishScience
EnglishScience
English
**
******
Total******

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #666699, align: center"]Date[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #dce6f1, align: right"]29/01/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #dce6f1, align: right"]2/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #dce6f1, align: right"]4/02/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]5/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #dce6f1, align: right"]8/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]9/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #dce6f1, align: right"]10/02/2016[/TD]

[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]11/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #dce6f1, align: right"]12/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]15/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #dce6f1, align: right"]16/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]17/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #dce6f1, align: right"]18/02/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]19/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="bgcolor: #dce6f1, align: right"]22/02/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]23/02/2016[/TD]

[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="bgcolor: #dce6f1, align: right"]24/02/2016[/TD]

[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]25/02/2016[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="bgcolor: #dce6f1, align: right"]26/02/2016[/TD]

[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #dce6f1, align: right"]1/03/2016[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="bgcolor: #dce6f1, align: right"]2/03/2016[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="bgcolor: #dce6f1, align: right"]3/03/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="bgcolor: #dce6f1, align: right"]4/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="bgcolor: #dce6f1, align: right"]7/03/2016[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="bgcolor: #dce6f1, align: right"]8/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="bgcolor: #dce6f1, align: right"]9/03/2016[/TD]

[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]

[TD="bgcolor: #c0c0c0"]CAT[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="bgcolor: #dce6f1, align: right"]10/03/2016[/TD]

[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #c0c0c0"]CAT[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="bgcolor: #dce6f1, align: right"]11/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="bgcolor: #dce6f1, align: right"]14/03/2016[/TD]

[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="bgcolor: #dce6f1, align: right"]15/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #ffcc00"]Inv Math[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="bgcolor: #dce6f1, align: right"]16/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]

[TD="bgcolor: #c0c0c0"]Sport[/TD]

[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="bgcolor: #dce6f1, align: right"]17/03/2016[/TD]
[TD="bgcolor: #00ccff"]PDH[/TD]

[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="bgcolor: #dce6f1, align: right"]18/03/2016[/TD]
[TD="bgcolor: #ffff00"]Maths[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #c0c0c0"]CAT[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="bgcolor: #dce6f1, align: right"]21/03/2016[/TD]
[TD="bgcolor: #99cc00"]Graphics[/TD]
[TD="bgcolor: #969696"]HSIE[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]
[TD="bgcolor: #ffcc00"]Inv Math[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="bgcolor: #dce6f1, align: right"]22/03/2016[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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

*MNOPQ
SubjectLast DateLessons missed since last attendanceAnswer should beTotals (Index/Match)
Maths
English
Science*#N/A
Inv Math*#N/A
Graphics
HSIE*#N/A
CAT*#N/A
PDH*
Sport*#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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3/03/2016[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #ffff00, align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]21/03/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]21/03/2016[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]22/02/2016[/TD]

[TD="bgcolor: #ffcc00, align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2/03/2016[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #99cc00, align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3/03/2016[/TD]

[TD="bgcolor: #969696, align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]3/03/2016[/TD]

[TD="bgcolor: #c0c0c0, align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]28/01/2016[/TD]

[TD="bgcolor: #00ccff, align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]17/02/2016[/TD]

[TD="bgcolor: #c0c0c0, align: right"]8[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Subject[/TD]
[TD]Last Date[/TD]
[TD]Lessons missed since last attendance[/TD]
[TD]Answer should be[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Maths[/TD]
[TD]
3/3/2016​
[/TD]
[TD]
10​
[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]English[/TD]
[TD]
3/21/2016​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Science[/TD]
[TD]
3/21/2016​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Inv Maths[/TD]
[TD]
2/22/2016​
[/TD]
[TD]
13​
[/TD]
[TD]
13​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Graphics[/TD]
[TD]
3/2/2016​
[/TD]
[TD]
10​
[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]HSIE[/TD]
[TD]
3/3/2016​
[/TD]
[TD]
9​
[/TD]
[TD]
9​
[/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]CAT[/TD]
[TD]
3/3/2016​
[/TD]
[TD]
6​
[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]PDH[/TD]
[TD][/TD]
[TD]
7​
[/TD]
[TD]
7​
[/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]Sport[/TD]
[TD]
2/17/2016​
[/TD]
[TD]
8​
[/TD]
[TD]
8​
[/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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


PDH EMPTY ERASE "NEVER"
 
Upvote 0
Solution
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

*MNOPQ
SubjectLast DateLessons missed since last attendanceAnswer should beMARZIOTULLIO's method
Maths
English
Science
Inv Math
Graphics
HSIE
CAT
PDH
Sport

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3/03/2016[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #ffff00, align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]21/03/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]21/03/2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]22/02/2016[/TD]
[TD="align: right"]13[/TD]
[TD="bgcolor: #ffcc00, align: right"]13[/TD]
[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2/03/2016[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #99cc00, align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]3/03/2016[/TD]
[TD="align: right"]9[/TD]
[TD="bgcolor: #969696, align: right"]9[/TD]
[TD="align: right"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]3/03/2016[/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: #c0c0c0, align: right"]6[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]29/01/2016[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #00ccff, align: right"]7[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]17/02/2016[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #c0c0c0, align: right"]8[/TD]
[TD="align: right"]8[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
T
  • Question Question
Report dealing with time & date calculations (Ugh!)
Replies
5
Views
602
TheRookie
T
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
S
  • Question Question
Search a table and return rows based on criteria of 2 cells
Replies
1
Views
718
Fluff
Fluff
A
  • Question Question
I need get output using excel formuals
Replies
3
Views
667
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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