• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT month and year

A

arey

New Member
Hello all,


I'm using the formula below to get the totals of 2 values based on the month and year. This works great right now. I want to change it a bit.

In another cell (A1) I have a pull down list that contains month and year (ex: January 2011). I want to use the formular below but where "December 2010" I want to get the totals based on the month and year selected in A1. Any help would be great.


=SUMPRODUCT(--(TEXT(Master_Log!$AI$3:$AI$1015,"mmmm yyyy")="December 2010")*(Master_Log!$AD$3:$AD$1015="Table Templates")*(Master_Log!$AH$3:$AH$1015="Boston")))
 
Arey


Is your list of Dates for the Drop Down in A1 a list of Dates or Text values?

Are the values in Master_Log Col AI any dates or just start of month dates eg: 1 Jan, 1 Feb etc


If it is Dates try:

=SUMPRODUCT((Master_Log!$AI$3:$AI$1015=A1)*(Master_Log!$AD$3:$AD$1015="Table Templates")*(Master_Log!$AH$3:$AH$1015="Boston"))


=SUMPRODUCT(((Year(Master_Log!$AI$3:$AI$1015)=Year(A1))*(Month(Master_Log!$AI$3:$AI$1015)=Month(A1))*(Master_Log!$AD$3:$AD$1015="Table Templates")*(Master_Log!$AH$3:$AH$1015="Boston"))
 
You must log in or register to reply here.
Back
Top