Jan 29 2022 03:00 AM
Pauly | NP | 12-Feb-21 | JOANA | ||||||||
Trizia | BL | 18-Mar-21 | IP | 1 | =SUMPRODUCT((A1:A15=D1)*(B1:B15=D2)) | ||||||
Myke | BL | 17-Mar-21 | Jan-21 | 0 | =SUMPRODUCT(($A$1:$A$15=D1)*($B$1:$B$15=D2)*($C$1:$C$15=D3)) | ||||||
JOANA | IP | 12-Jan-21 | |||||||||
Trizia | BL | 12-Mar-21 | |||||||||
Lee | BL | 13-Feb-21 | |||||||||
Pauly | SOA | 09-Feb-21 | |||||||||
Pauly | C220 | 10-Jan-21 | |||||||||
JOANA | RP | 12-Mar-21 | |||||||||
Lee | BL | 15-Jan-21 | |||||||||
Lee | RP | 12-Jan-21 | |||||||||
JOANA | BL | 09-Feb-21 | |||||||||
Myke | BL | 29-Jan-21 | |||||||||
Trizia | NP | 29-Jan-21 | |||||||||
Myke | NP | 12-Jan-21 |
Jan 29 2022 03:37 AM
Jan 29 2022 04:51 PM
Jan 29 2022 04:55 PM
Jan 30 2022 05:51 AM
In general for such counting it's better to use PivotTable
If formula
=SUMPRODUCT(
($A$2:$A$16 = D2) *
($B$2:$B$16 = D3) *
( MONTH($C$2:$C$16) = MONTH(D4) ) )
here
Jan 31 2022 04:52 AM
Jan 31 2022 05:17 AM
I see. As a comment, you may cube formulae in dashboard. That's more for complex measure or complex data model, in your case perhaps regular formulae works better. Depends on dashboard.
Idea for use cube formulae - creating PivotTable add data to data model, create PivotTable and convert it to formulas. You may use these ones.
Or bit more flexible. For such layout
it could be
=CUBEVALUE(
"ThisWorkbookDataModel",
"[Measures].[Count of Mark]",
"[Range].[Name].[" & $K$2 & "]",
"[Range].[Date (Month)].[" & $K$3 & "]",
"[Range].[Mark].[" & J$5 & "]")
Jan 31 2022 05:00 PM
Feb 02 2022 12:48 AM
@mrcuenco , you are welcome