Sum cells that are in current Month in Microsoft Excel-CodePudding

I wish to calculate MTD (Month to Date) of some products. Let's say my data is

   Product   Sale   Date
  A          2     27/01/2022
  B          4     26/01/2022
  A          1     14/12/2021

I wish to sum A products that are sold this month. So my Sumifs function have two criteria. I tried use the below formula but it seems the month function is not working. Any tip is greatly appreciated

=SUMIFS(!B:B,!A:A,"A",!C:C,MONTH(TODAY()))

CodePudding user response：

A possible option is to create a new column "is today's month" with the formula =IF(NUMBERVALUE(MONTH(TODAY())-MONTH(C2))=0,1,0)

Then use =SUMIF(D2:D4,"1",B2:B4)

CodePudding user response：

so you may use any one of the following,

=SUMPRODUCT((MONTH($C$2:$C$4)=MONTH(TODAY()))*($A$2:$A$4=B6)*($B$2:$B$4))
=SUM(IF((($A$2:$A$4=B6)*(MONTH($C$2:$C$4)=MONTH(TODAY()))),$B$2:$B$4))
=SUM(INDEX($B$2:$B$4*(TEXT($C$2:$C$4,"MMM")=TEXT(TODAY(),"MMM"))*($A$2:$A$4=B6),,))

Kindly remember you need to increase the range, i have just shown as per the sample data provided, hope it helps

Three Alternative Formula

CodePudding user response：

The sum of sales for product "A" in the current month:

=SUMPRODUCT($B$2:$B$4,(A$2:A$4="A")*1,(MONTH($C$2:$C$4)=MONTH(TODAY()))*1)