reference multiple sheets

hi,
I have an excel file with a lot of sheets in it. One sheet is named "main" and the other ones are named: 1, 2, 3, 4, 5, etc...

I want to put the following function in the "main" sheet:

=COUNTIF('1'!M2:M23,B1 & A2) + COUNTIF('2'!M2:M23,B1 & A2) + COUNTIF('3'!M2:M23,B1 & A2) + etc...

The question is, is there a way to somehow tell excel to run the function to sheets 1 to 31 without typing a very very long function like above?

Thanks a lot.
Eyal.

Eyal, conditional 3D summation/count is non-trivial.

John McGimspey outlines a variety of approaches: http://www.mcgimpsey.com/excel/threedsumif.html

Although the above relates to SUMIF the same logic holds true for COUNTIF.

Note: the SUMPRODUCT approach though elegant is inefficient and Volatile.

This involves adding a new function to your workbook. If you're OK with that, this would provide a notably simpler formula.

Here's a a UDF offered from here:
http://www.mrexcel.com/forum/showthread.php?t=36082#4

Public Function COUNTIF3D(rng As Range, V As Variant, ParamArray arglist() As Variant)
'Code by Jaafar Tribak via MrExcel.com
Application.Volatile
    COUNTIF3D = 0
    For Each arg In arglist
    COUNTIF3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + COUNTIF3D
Next
End Function

==========
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.

The new COUNTIF() formula would then be:
=COUNTIF3D(M1:M32, B1 & A2,"1","2","3","4","5","6","7","8","9","10")[/QUOTE]

I've only entered 10 strings for sheet names, you enter all the ones you need.

Here's an alternate version that lets you enter the sheet names into a range of cells. Might be easier for you to maintain, up to you:

Public Function COUNTIF3D(rng As Range, V As Variant, SheetNames As Range)
Dim cell As Range
    For Each cell In SheetNames
        If Evaluate("ISREF('" & cell & "'!A1)") Then _
        COUNTIF3D = WorksheetFunction.CountIf(Sheets(cell).Range(rng.Address), V) + COUNTIF3D
    Next cell
End Function

Possibly then used as:
=COUNTIF3D(M1:M32, B1 & A2, C1:C31)

Post up your book with the UDF in it and it not working, I'll take a look.

I thought you needed help with your implementation of the second macro. I don't see where you set up the sheet name range.

Did you examine the sample workbook?

Do you know what Named Ranges are? Look over in column AA and you'll see a list of sheet names 1-31. If you highlight all the numbers, you'll see "Sheets" appear in the Name Box (just to the left of the formula bar).

By naming a range of cell, you can use the name in the formula instead of a cell references. So the formula uses "Sheets" in the final parameter instead of the range of cells AA2:AA32. I suppose the cell reference is fine, too.

I'm finding the UDF I wrote is acting weird. For some reason it is declaring a circular reference when we try to use the column M, I'll have to work with it more when I get a chance. You can use it as is just skip column M on the Monthly Report (hide it).

Or, you can go back to using the original UDF, just break it up into two formulas since you can't have more than 27 array parameters in one function.

1) a slight tweak to the current function:

Public Function COUNTIF3D(Rng As Range, v As Variant, SheetNames As Range)
'JBeaucaire  (10/12/2009)
Application.Volatile
Dim cell As Range
    For Each cell In SheetNames
        If Evaluate("ISREF('" & cell & "'!A1)") Then _
        COUNTIF3D = WorksheetFunction.CountIf(Sheets(cell).Range(Rng.Address), v) + COUNTIF3D
    Next cell
End Function

2) Although I like trying to write applicable new functions, I really think the solution lies in using a more standard sheet design. Having 31 separate days of data that you want to analyze as a single data set is obviously cumbersome.

I would suggest you use a single data sheet for this. Add a new column A called "day" or "date" and put the 1-31 in there as you go down the sheet all through the month

Then your summary page can use simple sumif/countif and eliminate all of this. You can have an autofilter active on the data sheet so with the click of dropdown you can instantly be viewing only a certain, too, just like you would with separate sheets but without needing to have separate sheets.

In this sample sheet I've activated Excel's "List" feature on the dataset in the new sheet called "Data". The List feature bounds the dataset as group and offers an expansion row at the bottom. If you select a new day in that expansion row, Excel will instantly expand the dataset to permently include the new row and copy all the formulas and formatting to the new row without you having to do any of that. Very slick.

Use the dropbox in column A to filter the data anytime you want to see a smaller subset.

All the formulas on your Monthly Report sheet are simplified to match. Each colored section is a different formula.

I would find THIS data simpler to maintain by a longshot.

The sheet I attached to the last post is pretty much ready to use. Since the UDF isn't needed, I would remove all the code so the sheet stops asking you about enabling macros. Be sure to delete the module, just emptying it will not be enough.

Then just use the sheet.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

No, there's nothing particularly fancy in the individual columns of the final sheet, as a whole it may work slick, but individually you should be able to pick apart those formulas pretty easily.

You can read up about Excel "List" feature by pressing F1 in Excel.