Excel macro to fill a matrix with sums from cells across sheets with conditions

Question

I have an excel workbook with on sheet for each day (not all consecutive days) , that looks like ExampleSheet1. All sheets contain an empty list events, each with a unique ID and a Name. If the event occurs the time start and end time are noted and Excel calculates the duration of the event (among others).

ExampleSheet1

   A         B        C            D        E        F
1  id        name     date         start    end      duration
2  100       foo      10.07.2013   16:00    18:30    2,50
3  101       bar      10.07.2013                         0,00
4  102       aaa      10.07.2013   12:00    3:30     15,50
5  103       bb       10.07.2013   10:00    12:00    2,00
6  104       ccc      10.07.2013   9:00     11:00    2,00

I want to automate the calculation of a overview, where the duration of each event is summed up per month like in columns C,D and E of ExampleSheet2.

ExampleSheet2

   A       B       C            D               E        
1  id      name    total in     total in        total in
                   2013-07      2013-08         2013-09
2  100     foo     12,00        34,00           ...
3  101     bar     32,00        12,50           ..
4  102     aaa     35,50        123,00
5  103     bb      3,00         5,00
6  104     ccc     8,00         4,50

SUMIF does not work with multiple sheets so my first thought was to call a function like the following from each row in columns C, D and E of ExampleSheet2. But that would result in quite a few iterations and seems to be a very ugly solution.

function counter(targetID As Integer, targetDate As Integer)

    Integer i = 0
    for each worksheet in workbook(
        for each row(
            if column1 == targetID; then
                if month(column3) == targetDate; then
                    i = i + column6
        )

    )
    return i

As I'm not familiar with Excel macros and have to Google&Paste this code together, I'd apprechiate a hint on how to approach this. Thanks.

what is the date format in column C? Do Range functions .Value and .Text display the same result? — user2140173
@mehow: The date format (as in right click --> format cells) is category "date" with type "*14.03.2001". Value and Text both return "10.07.2013" — bison
try the code from my answer see if its work, if it doesnt then post further problems — user2140173

ApplePie ApplePie · Accepted Answer · 2013-05-08T11:11:39

You don't need to use VBA. You can use a mix of SUMIF and INDIRECT to achieve a SUMIF on multiple worksheets. In the following example, I had my dummy ids in column A of Sheet1 and Sheet2 and my dummy data on column B of those sheets. In column F of a third sheet I had my sheet names, in column C I added this formula:

=SUM(SUMIF(INDIRECT($F$1:$F$2&"!"&"A1:A100"), A1, INDIRECT($F$1:$F$2&"!"&"B1:B100")))

Now this is an array formula so when you enter it you want to hit CTRL + SHIFT + ENTER in order for it to calculate as an array, otherwise you will get only the sum on the first page.

This solution is much *much* cleaner. The only "dirty" part of it is that you need to list sheet names somewhere in your workbook to reference them. If you have a lot of worksheets you can use a simple macro to loop through all worksheets and output their name to form the list.

Finally, you need to be aware that you need to add the sheet names in your list everytime and to update the ranges to make sure to get everything in your formula.

Edit: It goes without saying that you should adapt my formula to your specific needs. I assume that you know how to use SUMIF and SUMIFS so you should have no problem doing this now that you know how to adapt it for multiple worksheets.

Excel macro to fill a matrix with sums from cells across sheets with conditions

1 Answers