Need Help Combining/Nesting 2 Formulas

[Justin_time]

Hi, I need assistance combining or nesting two formulas.
I am pretty new to Excel and writing formulas, and although I was able to find and modify each formula to do what I need them to do independently, I have had no luck with trying to put the two of them together in a single formula.

Here are each of the formulas, in their individual working states:

=IF(ISREF(INDIRECT("'Cost Summary'!A1")),INDIRECT("'Cost Summary'!A1"),"")
AND
=SUMIFS(S6:S1000,J6:J1000,"Non-Billable",N6:N1000,3004)

The second formula (the SUMIFS) currently resides on the "Cost Summary" tab in my workbook, and works as it should there.
The first formula (with the INDIRECT references in it), is on the first tab in my workbook and also works as it should.

My goal is to have the first formula combined with the second one so that when I create (from scratch each month) the "Cost Summary" tab, the data from the "Cost Summary" tab will automatically be filled into those cells which contain the "combined version" of the formula on the first tab of my workbook.

I am not sure if the SUMIFS should replace the IF, or if the SUMIFS would be nested inside of the "()'s" after INDIRECT.

I also know that some part (or all) of the SUMIFS formula will need to replace the !A1, but try as I might, I have not been able to figure it out.

... any help is greatly appreciated. Thank you.

[mikeTRON]

You should post a sample workbook.

[FDibbins]

Not really sure what you want, but perhaps...
=IFERROR(INDIRECT("'Cost Summary'!A1"),SUMIFS(S6:S1000,J6:J1000,"Non-Billable",N6:N1000,3004),"")

Or even...
IFERROR(SUMIFS(S6:S1000,J6:J1000,"Non-Billable",N6:N1000,3004),"")

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Justin_time]

Thank you mikeTRON & Ford. I have gone back to the original post and uploaded a sample workbook, with a couple of comments added inside of it to try and explain what I am trying to accomplish.

[FDibbins]

I will work on a quick formula for you, but why are youy running that INDIRECT test in teh 1st place? It seems unrelated to what you want to sum

[FDibbins]

OK, based on what you have in J, you only have Billable and Non-Billable, but (IF I understand, you want to sum Billable PLOTS"
Likewise, I dont see where you are showing a Prod-Code for that criteria?

Based on the formula you have in Cost sheet...
=SUMIFS(S6:S1000,J6:J1000,"Billable",N6:N1000,3002)
You are looking for Billable (OK I get that, that word can be extracted from K&W A10. But where does the 3002 come from?

Regarding your need for INDIRECT "Because the Cost Summary tab is generated on the fly each month", why not just keep that sheet and juct copy/paste the new data onto it, possible at the bottom of the existing data? This way, you will have all your data and can run historic summaries if needed. the SUMIFS can be modified to include dates

[Justin_time]

Hi Ford. I have very limited knowledge of Excel formulas, and in my searching of the internet, the INDIRECT formula seemed to be what I need.

I am trying to write a formula that will stay in the same cell on the K&W tab, and each month as the cost summary tab is generated, the formula will look at the cost summary tab and fill in the data I need automatically. In my example, if I rename the cost summary tab to something else, the word "Test" in cell J8 will disappear. Once the cost summary tab is present in the workbook, the contents of the cell that it points to will appear right on que.

What I would like to appear in cell C10 on the K&W tab is the sum from the cost summary tab for column "S" once the two criteria from columns "J" and "N" are met with the SUMIFS formula. I am looking for the total number of Sq. Ft. for the Billable Plot items (criteria "Billable" from column "J"), and the product code 3002 (criteria 3002) from column "N". The answer that I am looking for is = 4587.

It seemed simple enough to me that the two formulas that I already have could be put together in a single formula to do what I want (which is to wait for the cost summary tab to appear, then use SUMIFS to extract the numbers that I need).

As it is, I have to filter the data on the cost summary tab by columns "J" & "N" and then get my totals from column "S" to fill in the appropriate cells on the K&W tab. I am trying to automate this process.

[FDibbins]

OK to answer your question directly, perhaps this...

=IF(ISREF(INDIRECT("'Cost Summary'!A1")),SUMIFS(S6:S1000,J6:J1000,"Non-Billable",N6:N1000,3004),"")

Or maybe even...
=IFERROR(SUMIFS(S6:S1000,J6:J1000,"Non-Billable",N6:N1000,3004),"")
(which I mentioned before)

[Justin_time]

Good morning Ford. Unfortunately I am still doing a poor job of explaining what I am trying to do. Neither of the two formulas above do what I need.

The first one you provided I believe that I already had tried on my own. The problem with it I believe lies with the transition from referencing cell "A1" on the Cost Summary tab to instead referencing the SUMIFS result which should be pulled from the Cost Summary tab. The formula as you give above, with the comma between ...A1")),SUMIFS... has the indirect part of the formula looking for cell A1 on the Cost Summary tab and returning its contents, and the SUMIFS formula is returning its results from columns "J" "N" & "S" on the K&W tab. I don't want the contents of cell A1 - it is only in this workbook for me to test if and how the INDIRECT formula works pulling data from a tab called Cost Summary when the tab is either present or missing.

I now need to remove the A1 cell reference and replace it with the result of the SUMIFS formula to return the number 4,587 from the Cost Summary tab.

The second formula is still only referring to the tab on which it is written. If the second formula could be written in a cell on the K&W tab, and just sit there waiting until the Cost Summary tab is added to the workbook, and then pull the result, that would be perfect.

The only information that I have found online about writing a formula that refers to a tab that has not been created in a workbook yet mentions the INDIRECT formula. I don't fully understand what the INDIRECT formula does exactly. If there are other suggestions as to how accomplish this, I am open to any and all suggestions.

Thank you.