An oldie but goodie with a slight twist (I don't need to do a sumif ON the pages. I need to do a sumif ACROSS the pages).

I have an application that does staffing analysis. It consists of a variable number of sheets: one for each of the company’s offices and a summary page with the company-wide totals.

The first part is straightforward. I discovered that sum(sheetA:sheetZ!A1) will sum all the cell A1’s on sheetA and sheetZ and any sheet created or moved in between. This is good news since I want to give the end users the ability to add new offices. All they have to do is copy someone else’s page, rename it, and put in their own numbers. Everything “burns through” to the summary page.

At the bottom of the spreadsheet, each office does a breakout of figures based on staffing agency. No office has more than 5 staffing agencies supporting it, but company wide, there are about 25 staffing agencies.

So I provided a parameters page where the corporate HR gurus can add staffing agencies to an array. The array has two columns, an agency number and an agency name. I use a calculated string and indirect() to address the list dynamically, so HR can add as many agencies as the sheet will hold.

Each office tracks 4 rows of information for each agency supporting it.

When the users set up their sheets, they type the agency number in a cell in column A and the sheet fills in the agency name in column B. It also populates the next 4 rows in column A with a unique identifier to support the 4 rows of tracked data. I label the rows #A through #D – in other words, if the agency number is 12 these 4 rows are 12A through 12D. This makes the rows usable by vlookup, countif, sumif, etc. The users have up to 5 agencies and can hide the unused rows. They also get to hide column A when they are done with the setup.

Everybody is still happy up to this point.

Now the problem comes in.

I have to report all 25 staffing agencies on the summary sheet. I tried using Sumif like so: sumif(SheetA:SheetZ!A:A,<company number>,SheetA:SheetZ!D:D This didn’t work, of course.

I can populate a 100-row array on each office sheet at setup time by enumerating the agency numbers (plus their associated A-D rows) “off to the side” and using vlookup against the fixed 5 agencies I give each office. Then I can go back to using plain sum (SheetA:SheetZ!<cellname>) to get the totals on the summary sheet.

If I’m going to go this far, I just might as well hard code all the agencies on each office sheet, and tell the end user to hide the rows for the 20 or so agencies they don’t use.

So there is a solution to the problem, but it’s ugly, and it isn’t dynamic enough. I would like to leave room for growth.

Hi Dan,

I might be oversimplifying the issue, but could you not use a UDF to calculate the values?

For example...

Once again, thanks.

I am not sure of the incantation.

All my data is contained in worksheets between the sheets "Start" and "End" (which of and by themselves are dummies). This doesn't matter since, if I read your code correctly, it does the search on all sheets other than the sheet from which it is called. This is OK.

I am looking to sum all the items in column D where the value in column A is "1A." So I am calling it like so:

SUMA("1A",A:A,D:D)

Is this the correct way to call it? When I do this I get the #Value! error.

Hi again Dan,

I probably should have explained usage.

The first argument is a string, used to define the look up range, like the first argument in a SUMIF.
The second argument is a single cell range, used as the look up value, like the second SUMIF argument.
The third argument is a string, used to define the return range, like the 3rd argument in a SUMIF.

The only difference in the usage in a SUMIF and this UDF is that you are passing strings to the 1st and 3rd arguments instead of ranges.

So...
=SUMA("A:A",A1,"D:D")

Hope that explains it a bit better.

AHA! I figured it was something like that. I did try the other invocations ... but I would have never figured out putting quotes around the range "string!"

That makes it work well. Thanks for giving me an eligant solution!

OK now I have a new twist. I can see in the code above that you avoid the circular reference by adding the line:

If Application.Caller.Parent.Name <> ws.Name Then ...

Now the boss wants to do things not only by total company but also do sub-totals by regions. For the purposes of this question we'll say that a particular region consists of multiple offices in sheets between worksheet x and worksheet y.

So I have to play with the For Each ws In ThisWorkbook.Worksheets line.

Is there a way to loop by worksheet name so as to get all worksheets between worksheet x and worksheet y inclusive (or exclusive -- I have hidden dummy worksheets to separate the offices into regions for my SUM('worksheet x:worksheet y'!Cellrange).

Obviously worksheet x and worksheet y will have to be passed as parameters.

Nevermind. I figured it out. I added the starting and ending sheet names as the last two parameters and modified the code to read:

Function SumASht(strTest As String, rngCrit As Range, strSum As String, StartSheet As String, EndSheet As String) As Double
Application.Volatile
Dim ws As Worksheet, StartIndex As Integer, EndIndex As Integer
StartIndex = Worksheets(StartSheet).Index
EndIndex = Worksheets(EndSheet).Index
For Each ws In ThisWorkbook.Worksheets
If ws.Index >= StartIndex And ws.Index <= EndIndex Then
SumASht = SumASht + Application.WorksheetFunction.SumIf(ws.Range(strTest), rngCrit.Value, ws.Range(strSum))
End If
Next
End Function