Forum Discussion

mrcuenco's avatar
mrcuenco
Copper Contributor
Jan 29, 2022

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...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 29, 2022

mrcuenco 

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

  • mrcuenco's avatar
    mrcuenco
    Copper Contributor
    Jan 29, 2022
    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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 30, 2022

      mrcuenco 

      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

       

      • mrcuenco's avatar
        mrcuenco
        Copper Contributor
        Jan 31, 2022
        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.