Changing Sum Range Based on Date Chosen from Drop Down List
 

Hi,

I am trying to find a way to simplify the nested if i wrote below. I want my calculation to be done based on the date I input. My values are stored in a table as follows:

Jul-13 Aug-13 Sep-13 Oct-13............Total
Value1 Value2 Value3 Value4........ Sum(n0:n+k)

A1: Input Date

=IF($A$1="Jul-13",SUM(Q4),IF($A$1="Aug-13",SUM(Q4:R4),IF($A$1="sep-13",SUM(Q4:S4),IF($A$1="Oct-13",SUM(Q4:T4),IF($A$1="Nov-13",SUM(Q4:U4),IF($A$1="Dec-13",SUM(Q4:V4),IF($A$1="Jan-14",SUM(Q4:W4),IF($A$1="Feb-14",SUM(Q4:X4),IF($A$1="Mar-14",SUM(Q4:Y4),IF($A$1="apr-14",SUM(Q4:Z4),IF($A$1="May-14",SUM(Q4:AA4),IF($A$1="Jun-14",SUM(Q4:AB4),"Error, Please Check Date"))))))))))))

Regards,

ME

Changing Sum Range Based on Date Chosen from Drop Down List
 

Hi,

Am Tue, 18 Nov 2014 05:36:14 -0800 (PST) schrieb
:

Jul-13 Aug-13 Sep-13 Oct-13............Total
Value1 Value2 Value3 Value4........ Sum(n0:n+k)

A1: Input Date

try:
=SUM(OFFSET($Q$4,,,,MATCH(A1,Q3:AZ3,0)))
or
=SUM(INDIRECT("Q4:"&ADDRESS(4,MATCH(A1,A3:AZ3,0))) )


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional