Sum Amounts Sold During Month & Year

You have a table of products, sales dates, and amounts:

sum_P_D_Y

Objective: Sum all sales amounts , from the same product, during a certain month and year.

Input in yellows cells only, any date within the month and year to be summed and a product code:

The answer has two formulas that are the same formula except one assumes the data is in a Table and one formula if the data is not in a Table:

Non – Table:

Table:

In addition I conditionally formatted the table to highlight the rows that fit the criteria with this formula:

 

Download workbook “Sum_within_month_and_year” from:

http://www.pistulka.com/Excel_Shared/ 

Downloads Written in Excel 2013

 

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

2 Comments

  1. sumifs is a little clearer

    =SUMIFS(C:C,A:A,I3,B:B,”>”&EOMONTH(I2,-1),B:B,”<="&EOMONTH(I2,0))

    1. Thanks Tom.
      You are correct, it results in a much shorter formula. I am old school (I’m 70 years old) and my default formula for many projects has always been sumproduct. Its hard to break old habits.

Leave a Reply to Tom Si Cancel reply

Your email address will not be published. Required fields are marked *