SUMIF with NAME RANGE

[Big_Tater]

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?

 

[Bob Phillips]

Try this alternative

=SUMPRODUCT(('Summary By Month'!$B$6:$B$37=$B6)*(INDIRECT($D$5)))