Excel – How to use INDIRECT with a SUMIF formula for a date range

microsoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

I'm trying to get a SUMIFS formula to dynamically work with multiple tabs in an excel workbook. I have numerous tabs in this workbook, each that contains client data I update daily. In the example below I have a client called "Joes 27" and on that sheet there is sales data listed daily. I am looking to have a summary page where I can enter a date range and have it sum up the sales data for that range for Joes 27. And then for me to be able to switch it to another client name. I use this currently:

=SUMIFS('Joes 27'!5:5, 'Joes 27'!2:2, ">="&B1, 'Joes 27'!2:2, "<="&C1)

"Joes 27" is the tab name of the client tab. Others are called "Citi 15" for example.
Row 5 in each client tab is what I want added up.
Row 2 in each client tab is the list of dates. (6/1, 6/2 , 6/3)
On the summary page, I enter the date range in B1 and C1 (6/1 6/3)

What formula can I use so I can enter a client name on the summary page and have it use the SUMIFS formula to add up the data in that range on that page. I assume INDIRECT function but I can't seem to figure it out.

I sincerely appreciate the help!

Best Answer

Try this out and see if it works:

=SUMIFS(INDIRECT("'"&A1&"'!5:5"),INDIRECT("'"&A1&"'!2:2"),">="&B1,INDIRECT("'"&A1&"'!2:2"),"<="&C1)

Related Solutions

Excel – Selecting the date of every Monday and Wednesday from a date range

Here's a formula that will do that.

In your first column, put your starting date.
In the next column, put this formula:

=IF(TEXT(A1,"ddd")="Mon",A1+2,A1+5)

Which says, if the previous column is a Monday, this column is that day + 2. If it's not, this column is that day + 5.

Then copy the formula into the subsequent cells until you've reached the last date of your quarter.

Here's a video I found that shows how to copy a formula: Quickly Copy/Paste Data, Formulas, Text in Excel Using the Quick Fill Handle

You can format that row so that the dates are displayed how you like by right-clicking the row number (which selects the whole row) and choosing Format Cells....

Under the Number tab, choose Date from the category list and then pick a type. Or you can pick Custom from the category list and customize it. I used this under custom:

ddd, mmm d

Which is display like this:

Mon, Sep 10

This page lists what each "code" means (i.e. "ddd" is the day abbreviated, "Mon"):

http://www.ozgrid.com/Excel/CustomFormats.htm

Excel – Indirect function for worksheet names in Excel

For:

A1 = Workbook

A2 = Worksheet

A3 = Cell Reference

Use =INDIRECT("[" & A1 & ".xls" & "]" & A2 & "!" & A3)

To reference a worksheet with a space you need to surround it in single quotes ('). For:

A1 = Workbook

A2 = Worksheet Name 1

A3 = Worksheet Name 2

A4 = Cell Reference

Use =INDIRECT("[" & A1 & ".xls" & "]" & "'" & A2 & " " & A3 & "'" & "!" & A4)

To reference a particular range in the sheet that you do not have recorded in a cell, simply place it alongside the !:

Use =INDIRECT("[" & A1 & ".xls" & "]" & "'" & A2 & " " & A3 & "'" & "!E34")

Note: With INDIRECT, you must reference only open workbooks

Related Question

Excel 2010: Formula for Sum and Return Date