Forum Discussion
Help. How can I total the BL, IP, NP, etc. per month using sumproduct. Thank you.
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...
D3 is custom formatted as mmm-yy, intended for the whole month. What I would like to get is the total of all BL, C220 etc. for the month. Further info column D3 is a list made in data validation. Thank you.
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
- Thank you very much Mr. Baklan for your suggestion to use Pivot Table. We have several forms/templates for our monthly reports and it is convenient for me to use formulas that automatically fill out those templates. I tried Pivot Table and I can hardly adjust the forms/templates to suit for my report. Actually this is the only command that I can't solve, I just compute manually thru Microsoft Table.
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 & "]")