Help. How can I total the BL, IP, NP, etc. per month using sumproduct. Thank you.

Copper Contributor
PaulyNP12-Feb-21JOANA        
TriziaBL18-Mar-21IP1 =SUMPRODUCT((A1:A15=D1)*(B1:B15=D2))  
MykeBL17-Mar-21Jan-210 =SUMPRODUCT(($A$1:$A$15=D1)*($B$1:$B$15=D2)*($C$1:$C$15=D3))
JOANAIP12-Jan-21         
TriziaBL12-Mar-21         
LeeBL13-Feb-21         
PaulySOA09-Feb-21      
PaulyC22010-Jan-21         
JOANARP12-Mar-21         
LeeBL15-Jan-21         
LeeRP12-Jan-21         
JOANABL09-Feb-21         
MykeBL29-Jan-21         
TriziaNP29-Jan-21         
MykeNP12-Jan-21         
8 Replies

@mrcuenco 

Depends on what is in D3 - is that text or date formatted such way?

image.png

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

@mrcuenco 

In general for such counting it's better to use PivotTable

image.png

If formula

=SUMPRODUCT(
  ($A$2:$A$16 = D2) *
  ($B$2:$B$16 = D3) *
  ( MONTH($C$2:$C$16) = MONTH(D4) ) )

here

image.png

 

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.

@mrcuenco 

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

image.png

it could be

=CUBEVALUE(
  "ThisWorkbookDataModel",
  "[Measures].[Count of Mark]",
  "[Range].[Name].[" & $K$2 & "]",
  "[Range].[Date (Month)].[" & $K$3 & "]",
  "[Range].[Mark].[" & J$5 & "]")
Good Day Mr. Baklan!
I will research on this cube functions and I will try to use it. I am always open to suggestions to improve my work and myself too. Thank you very much for your time and sincerity in helping me. Thank you.