Unable to SUMIF across columns and rows

We have a monthly schedule that lists who is using a Sports Hall at certain times. What we would like to do is have a running total of how many hours the Cricket booking has used which is updated on each sheet.

I have included the Workbook an decided from the way the originator set up the sheets I would never be able to calculate the timings because there isn't a real value to work on because the timings are text not numerical - see worksheet FEB 2012.

In the OCT 2012 worksheet I have seperated the timings into two columns which I thought might help.

I get the gist of how the =SUMIF(A2:A10,"Cricket",B2:B10) works over two columns but I'm confused how to add up the time used by 'Cricket' when:
a. 'Cricket' could appear anywhere in the schedule
b. The period of use (hours) also needs to be included in the formula

Can anyone suggest a solution or perhaps a revamp of how the worksheets should be setup please. Each worksheet is printed out on an A4 sheet. The running total does not need to be printed out - we just need to be aware of when it has reached 100 hours and I assume Conditional Formatting would take care of this aspect

Since each entry "cricket" in the range B4:F41 is one hour duration, You can use the below formula.
The cell is to be as [h]:mm using custom.
The formula gives the total hours of cricket.
In OCT 2012 Sheet it is 20 hrs.

=COUNTIF(B5:F41,"Cricket")/24

Originally Posted by MarvinP

Hi Sandy,

I would NOT do a separate sheet for each month. I'd figure out how to print the table above on a different sheet but keep the data as above.

Then to get a total or running total, I'd do a Pivot Table and group by month or day or... and filter it to whatevey you want.

I'm tempted to convert your data but it will take a few hours. Is it real data? What should be done with the notes to the right side. How bad do you need an answer? I'm afraid after doing it like I want, "the boss" won't like it and make you go back to the page printout method.

Let me know what to do!

I'm a great believer of 'adopt, adapt and improve' and I am very much appreciative to any suggestions but I'd hate to put you to so much trouble by doing a total revamp of the workbook which would take forever.

I am actually trying to help the Bursar's Secretary (it's her spreadsheet) and thought that it would be a simple job but fell at almost the first hurdle! If you were to do one month as a template I could replicate your work for the other months to save time? The data is real data but we were thinking of running the new workbook/spreadshhets starting from the Acedmic Year (September).

I didn't quite understand the reason for not recording the info on monthly sheets on the first line of the quote above, and then you lost me completely on the second line!

THe Secretary prints each monthly Schedule for the Notice Board. And I'm not 100% sure that the Cricket periods will always be an hour - she says they will be but there's always that one time that crops up no matter what.

Hope that helps and doesn't just cloud the issue?

Originally Posted by kvsrinivasamurthy

Since each entry "cricket" in the range B4:F41 is one hour duration, You can use the below formula.
The cell is to be as [h]:mm using custom.
The formula gives the total hours of cricket.
In OCT 2012 Sheet it is 20 hrs.

=COUNTIF(B5:F41,"Cricket")/24

Thanks, I'll give that one a go as a short term remedy depending on Marvin's reply to post #4 - please also note my penultimate paragraph in that post reference period of time for Cricket

Originally Posted by MarvinP

Hi Sandy,

I've created some fake data and put it on a "New Method" sheet. It demonstrates where I'd be going. It took about 5 minutes to create but shows you what my thinking is. See it attached and decide if this is what you want to do with your real data.

Your five minutes is my 60 minutes!!

There are 2 sample pivot tables on this sheet. The one with running total hours answers the question. You can change the filter to show any of the Events/bookings.

Got the gist of this only after playing with it a while

I hope this helps..

After I looked at this a while - I wondered why they do it using Excel. It looks like an Outlook Calendar problem to me. The only problem with Outlook is totaling the hours.

I now understand what you meant by one table. What the Secretary is trying to do is produce a usage schedule which can be printed and put on the Notice Board so users can see who is on, who is due on and view forecast of usage for that month. She also wants to be able to keep a running total of Cricket hours for the year and each month (sheets) showing the total to that point. I think she may also need to keep an archive of usage for each month for the previous year.

Are there no further Excelsperts out there who could help me?