Prev: excel sortingNext: vLookup, Look up Value From: Big_Tater on 28 Mar 2010 18:50 Really need some help with this one as I am stuck. I am trying to use a drop down box and a name range to get monthly totals and can't figure it out. Here is what I have: 2 Sheets: Monthly Report Sum By Month On both sheets I have Agent IDs in Row \$B. On my 'Sum By Month'! sheet I have money earned in columns D:E, H:I, L:M, all the way through December. I gave each pair of revenue columns a range name according to months. (SUM_JAN (columns D:E), SUM_FEB (columns H:I), SUM_MAR (columns L:M) etc.) I want to pull the sum of each range from 'Sum By Month'! that match the Agent ID in Column \$B. I created a drop down list on the 'Monthly Report'! sheet in cell D5 so the agent can select the month they wish to see revenue earned for, but I am only getting the total from the first column in my name ranges. (ie: 'Sum by Month'! column D revenue shows, but not E.) Here is the formula I have so far, but I am missing something or using the wrong formula. =SUMIF('Summary by Month'!\$B\$6:\$B\$137,\$B6,INDIRECT(\$D\$5)) Again, D5 is where I have my NAME RANGE drop down list so if they select SUM_FEB is should show the total for February in cell D6, adding columns D:E from 'Sum by Month'! Any help or thoughts is VERY much appreciated? From: Bob Phillips on 28 Mar 2010 19:14 Try this alternative =SUMPRODUCT(('Summary By Month'!\$B\$6:\$B\$37=\$B6)*(INDIRECT(\$D\$5))) -- HTH Bob "Big_Tater" wrote in message news:85B28DA6-6C88-41F4-AEF8-5AFA0AEB0EED(a)microsoft.com...> Really need some help with this one as I am stuck. I am trying to use a > drop > down box and a name range to get monthly totals and can't figure it out. > Here > is what I have: > > 2 Sheets: > Monthly Report > Sum By Month > > On both sheets I have Agent IDs in Row \$B. On my 'Sum By Month'! sheet I > have money earned in columns D:E, H:I, L:M, all the way through December. > I > gave each pair of revenue columns a range name according to months. > (SUM_JAN > (columns D:E), SUM_FEB (columns H:I), SUM_MAR (columns L:M) etc.) I want > to > pull the sum of each range from 'Sum By Month'! that match the Agent ID in > Column \$B. > > I created a drop down list on the 'Monthly Report'! sheet in cell D5 so > the > agent can select the month they wish to see revenue earned for, but I am > only > getting the total from the first column in my name ranges. (ie: 'Sum by > Month'! column D revenue shows, but not E.) > > Here is the formula I have so far, but I am missing something or using the > wrong formula. > > =SUMIF('Summary by Month'!\$B\$6:\$B\$137,\$B6,INDIRECT(\$D\$5)) > > Again, D5 is where I have my NAME RANGE drop down list so if they select > SUM_FEB is should show the total for February in cell D6, adding columns > D:E > from 'Sum by Month'! > > Any help or thoughts is VERY much appreciated? >  |  Pages: 1 Prev: excel sortingNext: vLookup, Look up Value