Can not get my sumifs and indirect formula to output the proper value

[the machine]

Hello,

I'm working on a formula for spreadsheet variance analysis and running into an issue regarding my sumifs. I'm using indirect to reference a given sheet and it's working fine but I now need the final part of the formula to sum if the cells are either blank, Labor, or Material.

=ROUND(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A309,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),"=LABOR",INDIRECT("'"&B$2&"'!V1:V60000"),"=MATERIAL",INDIRECT("'"&B$2&"'!V1:V60000,""),0)

The bold and italicized part is what is not recording properly.

[AliGW]

Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
  
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[the machine]

I'm guessing that was some auto generated response to my thread since I used the work 'Help' in the original title, but I updated it now, so please feel free to help anyone.

I also tried to break the bolded area down by part, and each part works on its own, but not when I combine all of them into the formula.

[AliGW]

Thank you for changing your title. The text was boilerplate, but applied by me - I am a human.

Here's some more boilerplate (it saves me time):

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[the machine]

I did find a work around but it seems kind of absurdly long. Since each section works on its own, Blanks, Labor, Materials, I just recreated each section in whole and summed them. How can I break this down to be smaller as it will be copied across 1900 rows for 3-12 columns.

=ROUND(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A3,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),""),0) + ROUND(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A3,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),"LABOR"),0) + ROUND(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A3,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),"MATERIALS"),0)

[AliGW]

Attach a sample workbook, as advised.

[the machine]

I can't really do that, the file is pretty much full of confidential information.

I'll create a mock version, going to take a little while.

[AliGW]

OK, well bearing in mind I can't test this, see if this works for you:

=ROUND(SUM(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A3,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),""),0) + ROUND(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A3,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),{"LABOR","MATERIALS",0})),0)

[AliGW]

Did you see post #8?

[the machine]

Yes, I did, tried it, too few arguments. I'm going to upload a workbook, just need to remove all the cells not in use. Should have it in a few minutes.

[AliGW]

OK - I'll wait for the sample. Thanks!

[the machine]

file uploaded

[AliGW]

Seems to work:

=ROUND(SUM(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A17,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),{"","MACHINE","LABOR"})),0)

So, just to be clear, I've marked in red what has been added:

=ROUND(SUM(SUMIFS(INDIRECT("'"&B$2&"'!F1:F60000"),INDIRECT("'"&B$2&"'!N1:N60000"),$A17,INDIRECT("'"&B$2&"'!H1:H60000"),">0",INDIRECT("'"&B$2&"'!J1:J60000"),"<>11530",INDIRECT("'"&B$2&"'!V1:V60000"),{"","MACHINE","LABOR"})),0)

[the machine]

Yes, formula works properly now. Thank You for the help. It was the {} that I did not know about. Hopefully the workbook runs smoother now

[AliGW]

Maybe you missed them in post #8? They were there all along.

Glad to have helped!

If by "smoother", you mean "faster", it might, but then again, it might not: heavy use of INDIRECT is known to be somewhat problematic in this regard.

[the machine]

Yes, it was probably something where I didn't update the cell reference to the particular line I was in and it was pulling the incorrect C#

[AliGW]

Well, you are there now. You can stop tugging at your hair now!!!

Did you see what I just added to my last post? Might be worth bearing in mind.

[the machine]

Yes, I use indirect in another analysis spreadsheet I use, and it is slow, but this most recent one was beyond what I'm used to.