SUMIF with INDIRECT

[Big_Tater]

Hey all, I'm new here and have searched many forums for my answer but can't find one. Like this forum the best so registered and plan to stay.

Here's my problem:

I have 2 worksheets/tabs ('Monthly Report'! and 'Summary by Month'!) in my Workbook. Both sheets have column $B6:$B137 with Company IDs. I have sales revenue in 2 columns on "Summary by Month'! for each month which are named with a Range Name for each month. (ie. SUM_JAN = D6:E137, SUM_FEB = H6:I137, etc.)

I have a drop down list on the 'Monthly Report'! sheet in cell D5 so any agent can select the month they wish to see sales revenue for any company. I know I'm missing something simple, but I have been working on this for 2-days now.

Here is what I have, but it only gives me the revenue for column D not column E in my Range Name:
=SUMIF('Summary by Month'!$B$6:$B137,$B6,INDIRECT($D$5))

Again, D5 is where I have my Range Names in a drop down so the agents can see any month revenue by company.

Any help is REALLY appreciated. I'm going blind trying to figure this out.

[Shampoo Monkey]

Hey Big_Tater,

Best I can think of is there is something off with the ranges. If you replace the INDIRECT($D$5) with the actual name, does it work?

Named ranges can vary based on what worksheet or cell you are calling the range from. If it is a range that you want to remain the same no matter where you referencing it from... I recommend tethering it to the specific worksheet or creating a static range.

We can probably pin it down a bit more if we had an example workbook.

[Big_Tater]

Hey Big_Tater,

Best I can think of is there is something off with the ranges. If you replace the INDIRECT($D$5) with the actual name, does it work?

Yep. Tried that and got the same results.

We can probably pin it down a bit more if we had an example workbook.

Happy to provide a sample file for you to work with. I hacked together a quick look-alike sheet for ya. Maybe there is a better way to accomplish what I want to do, but I thought this would be easiest for multiple users who aren't familiar with spreadsheets.

The "0" company names are there for new companies that will be added soon. (We add to the list each month.) There are actually more columns between months, but this should do the trick.

[Huron]

Try

=SUMIF('Summary by Month'!$B$5:$B77,$B5,INDIRECT($C$4))+SUMIF('Summary by Month'!$B$5:$B77,$B5,OFFSET(INDIRECT($C$4),0,1))

[DonkeyOte]

Just to clarify... with SUMIF the dimensions of the (optional) sum_range will assume that of the requisite range - it will use the first cell of the sum_range (where specified) as the starting point, eg:

=SUMIF(A1:A10,"a",C1:D10)

is actually calculated as

=SUMIF(A1:A10,"a",C1:C10)

ie the sum_range dimensions will be the same as the initial range - in this case a 10x1 vector - thus the sum_range becomes a 10x1 vector commencing from C1.

=SUMIF(A1:A10,"a",C20:D29)

is calculated as

=SUMIF(A1:A10,"a",C20:C29)

the 10x1 vector persists but commences from C20 given this is first cell stipulated.

Note that though the below:

=SUMIF(A1:A10,"a",C1)

would work as

=SUMIF(A1:A10,"a",C1:C10)

it would be Volatile post XL2000 see: http://www.decisionmodels.com/calcsecretsi.htm


So, the point is that unless the "criteria" range and the summation range share the same dimensions you can't use a single SUMIF - you need to combine two (as per Huron's post)

=SUMIF(A1:A10,"a",C1:C10)+SUMIF(A1:A10,"a",D1:D10)

or use an Array/SUMPRODUCT

=SUMPRODUCT((A1:A10="a")*C1:D10)

The disadvantage in this present example is that your formula is utilising INDIRECT meaning whichever route you take it will be Volatile...
Volatile SUMPRODUCTs are generally best avoided as it (like a standard Array) is more "expensive" than your standard formulae and if Volatile it will calculate more often than is strictly necessary (most likely).

I concur with Huron that a combined SUMIF approach is best here (though least elegant option), you could in theory use SUMPRODUCT with an INDEX based approach here rather than INDIRECT (given your data layout is consistent) but loading a sheet with SUMPRODUCTs is generally best avoided.

[Big_Tater]

Huron, thank you very much that is exactly what I needed!

DonkeyOte, thank you VERY much for the explanation. I love Excel and the power of what it can do. Understanding the formulas really means a lot to me when I get assistance. I will definitely read the suggested links you have posted and will also look into the INDEX function as an alternative to the INDIRECT.

Thanks again for both of your help with this. Truly life savers!!!!