Prev: excel sorting
Next: 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" <BigTater(a)discussions.microsoft.com> 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 sorting
Next: vLookup, Look up Value