Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with NAME RANGE
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF with NAME RANGE
Try this alternative
=SUMPRODUCT(('Summary By Month'!$B$6:$B$37=$B6)*(INDIRECT($D$5))) -- HTH Bob "Big_Tater" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
SumIf - when I fill down the Range, Criteria & sum range changes | Excel Worksheet Functions | |||
SUMIF within a range | Excel Worksheet Functions | |||
How to use a range in SUMIF? | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |