SUMIFS across multiple sheets

[Dunnerz]

Hi all,

First post so hopefully going about this right. I've searched a bunch of the threads but can't seem to see what I'm doing wrong. Help would be appreciated.

- I have a table (Customer_Table) in cells A3:A12, with a list of customers. Each one has a corresponding tab name in the worksheet
- Next to this table, each customer is listed down the page in column H, with the date in column G (format mmm-yy)

i.e.

Column G Column H

Jan-16 customer 1
Jan-16 customer 2 ...
Feb-16 customer 1
Feb-16 customer 2 etc... for the full year

I need to lookup the customer tab (each tab is in the same layout), search for the date in column K and return a value in column L.

This is what I'm using. This returns the correct values for Jan for each customer, but as I drag it down to Feb and beyond I get a #VALUE! error

=SUMIFS(INDIRECT("'"&Customer_Table[Customer]&"'!L:L"),INDIRECT("'"&Customer_Table[Customer]&"'!K:K"),G3,INDIRECT("'"&Customer_Table[Customer]&"'!A:A"),H3)

L:L refers to the rate in each of the customer tabs
K:K is the date in each tab
A:A will have the customer name in its corresponding tab

Am I barking up the right tree here? Many Thanks!

 

[jorismoerings]

Hi and welcome to MrExcel,

I don't understand the logic you applied in reference to the question. Your formula scrolls through all customer sheets while you actually need to go to the specific sheet for that particular customer. Unless your customer sheet aren't customer specific but even than.

Try changing your formula to this:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Customer_table[Customer]&"'!L:L"),INDIRECT("'"&Customer_table[Customer]&"'!K:K"),G3,INDIRECT("'"&Customer_table[Customer]&"'!A:A"),H3))

and most important- make sure your table with customer in A3:A12 contains NO blanks because it will take cycle time through the sheets while there's actually nothing to cycle through.

 

[Dunnerz]

Thanks so much for the reply.

Completely understand what you're saying. You're correct the SUMPRODUCT is a better option, I think I was just trying different things out of frustration.

Your formula works the same for Jan, but the same issue applies. Once I copy the formula down, I get no results for the following months:

Column G Column H
Jan-16 Customer 1
Customer 2 Customer 2
Customer 3 Customer 3

 

[FDibbins]

SUMPRODUCT is essentially an ARRAY formula, so you should avoid using full-column references, it will start to slow your file down.

What do your dates look like, are they real dates or just text that looks like a date?

 

[Dunnerz]

Sorry my profile had a hissy fit and posted prematurely - Thanks so much for the reply.

Completely understand what you're saying. You're correct the SUMPRODUCT is a better option, I think I was just trying different things out of frustration.

Your formula works the same as the one I posted for January data, but the same issue applies. Once I copy the formula down, I get no results for the following months:

Col G Col H

Jan-16 Customer 1
Jan-16 Customer 2
Jan-16 Customer 3
Feb-16 Customer 1
Feb-16 Customer 2
Feb-16 Customer 3
.... etc etc



Once the formula is copied down to Feb, Mar etc, it just returns 0

There is definitely data in each tab which corresponds with each month. I've tried adjusting the formats of the dates in each tab but same issue. Also there's definitely no blanks in my table with all the customer names.

Any idea's? Thanks again.

 

[Dunnerz]

Thanks Ford,

Each file will only be for the Financial Year and this page will just be a summary so should be ok with Array's. Hopefully won't slow down too much, but if you have an alternative please let me know.

The dates I have tried both ways. Actual dates and also in txt format.

They are formula cells that run off an initial date cell so they all update based on once cell, but I've also tried hard coding them and the same thing happens.

 

[jorismoerings]

Hi,

Beside the remark from Ford in not using full-column references i can't find anything wrong with my formula.
So for the sake of testing i created a test model to verify.

Mine works: this is the link to it https://app.box.com/s/tlyyyvjqsthwg2qr285rzdm3dgidsr3h
Hope this helps

 

[Dunnerz]

Ok great, all sorted. Thanks so much.

For the record, the SUMPRODUCT did fix my issue. During my "experimentation" I altered the formula that was picking up the Customer names in column A of each tab and it was messing with the SUMIFS formula.

The example you sent through made me realize the additional error.

Once I changed both things my table worked.

Thanks again!

 

[jorismoerings]

glad i could help

 

[FDibbins]

The dates I have tried both ways. Actual dates and also in txt format.

They are formula cells that run off an initial date cell so they all update based on once cell, but I've also tried hard coding them and the same thing happens.

Can you test a few using =ISNUNBER(cell-ref)...FALSE indicates text

You guys use dd/mm/yy format, which may mean that some of your data - if it was imported - mA HAVE GOT SWAPPED AROUND