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

 

Comments

  1. Tom Si says:

    sumifs is a little clearer

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

    1. Don Pistulka Don Pistulka says:

      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

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

*
*

WordPress spam blocked by CleanTalk.