INDIRECT Function NOT capturing all of the references

I am using an INDIRECT function in my formula. Below is the whole formula that I am using

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Accounts&"'!D3:D190"),INDIRECT("'"&Accounts&"'!A3:A190"),A51,INDIRECT("'"&Accounts&"'!c3:c190"),">="&$S$10,INDIRECT("'"&Accounts&"'!c3:c190"),"<="&$S$11))

I made a list of the worksheets names, selected and named the cells "Accounts"

The INDIRECT Formula is not capturing all the worksheets that I referenced. (Total 12)

The formula counts all the worksheets except for the last 2

I checked the names, rewrote the names, rewrote the the reference cell name and nothing is working.

My last guess is that INDIRECT will only do a maximum of 10 cells for a list. Am I right? and if so will my formula be changed?


Thanks!!

It works for me, with small datasets on 12 sheets.

Can you post a sample book?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

If the formula is working at all, then the sheetnames are most likely valid.
With an invalid sheetname in the list, the formula would result in #Ref! error.

How do you actually know it's the last 2 that aren't being counted?
There must be something different about the data on those 2 sheets vs the other 10 that are counted.

I would make 2 seperate sumifs formulas, each using one of the 2 sheets, without using indirect.
And start doing a process of elimination be removing criteria from the formula.

Also check that your named range (accounts) actually does include ALL of the sheets.

Example.xlsx


I manually entered my desired results. Focus on the F column is where I named the worksheets to reference "aCCOUNTS". That is where I am having trouble in my other work, Where the formula will count the for Account1, but not Account2

I was hoping to see an attachment that contains a formula that produces the wrong result.

Originally Posted by Jonmo1

If the formula is working at all, then the sheetnames are most likely valid.
With an invalid sheetname in the list, the formula would result in #Ref! error.
How do you actually know it's the last 2 that aren't being counted?

There is no REF# Errors.
And I know that the two sheets are not counted because the total (Using same formula, except without the date range) is different from when I sum the months together. I was able to gather that the totals for certain products were only formed by these 2 accounts(Worksheet).

So the total column would say $500, whereas if I looked at the January, February, and other months I would see no calculation.

Originally Posted by Jonmo1

I was hoping to see an attachment that contains a formula that produces the wrong result.

Example with wrong result.xlsx

I tried to reproduce the wrong result, but to no avail, however you can now work with the formula, I am working with in February Column.

It's impossible to say what the issue is by looking at books that are actually working.
The formula is fine, and should work as desired.

There are 2 possible reasons I can see for the last 2 sheets to be 'skipped'.
1) The named range 'Accounts' doesn't actually include ALL the sheets.
If your sheetnames are actually in Say A1:A12, but the named range actually refers to A1:A10, then A11 and A12 are skipped.
Easiest way to tell if this is the issue is to type Accounts in the Name Box (to the left of the formula bar) and press enter.
That will then highlight the range that name refers to.

2) The 2 sheets are not actually being skipped. Yes, that's exactly what I mean.
But rather the SUMIF is indeed being performed on those sheets, but the results of them just happen to be 0.
Which would mean either none of the rows on those sheets met the criteria of the sumifs
Or the values in the sum range are not really numbers (or they just happen to add up to 0.. -2 +2 = 0)
I would step away from the big indirect formula, and just put a plain old sumifs on each sheet, and troubleshoot those.

I see that I have not gotten back to you and completely forgot about this post. I finally realized the problem today when I tried to rework this.

It was the formatting of my numbers i was summing. I REPEAT it was the formatting of my numbers. The numbers were in text and when I reformatted them to the numbers to "accounting" the sums showed up.

Sorry if I broke the rules by responding sooo late but nothing was wrong with the formula but with the data I was searching. Thanks for helping!!