multiple conditional sum? or average?

Cablelane

New Member
Joined
Mar 16, 2009
Messages
5
Any help on this appreciated. I'm new to this. Currently using Excel '03.

I have a workbook w/ one worksheet for each of ~20 employees to track patients they see. Each sheet is essentially the same: rows are for dates someone works, columns are “Date”, “Day of week” (calculated), “Scheduled”, “Cancel”, “No-show”, “CA/NS rate” (calculated). I’d like to come up w/ a department average, per month, on a separate “Tally” sheet. It may be helpful, down the road, to look at day of week or other variables. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
Solutions I’ve tried: <o:p></o:p>
1) pivot table: worked well on individual sheets, but couldn’t get it to calculate across all sheets, based on month.<o:p></o:p>
2) SUMIF: trying to add each column above onto the tally sheet, then recalculate CA/NS rate; again, couldn’t get it to conditionally add (in other words, 2/23/09 may be on row 2 for me, but not present at all on someone else’s sheet if they didn’t work at all, so I can’t just add up same cell on different sheets).

Thanks again for help!

<o:p></o:p>
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
edit: I think this works now

Code:
Sub CompileSheets()
Dim ws As Worksheet, wsTally As Worksheet, rngCopy As Range, i As Integer
 
Set wsTally = ThisWorkbook.Worksheets("Tally")
wsTally.UsedRange.ClearContents

i = 0
 
For Each ws In ThisWorkbook.Worksheets
    If ws.Name = wsTally.Name Then GoTo NextWs
        If i > 0 Then GoTo CopyValues
            Set rngCopy = ws.Range(ws.Cells(1, 1), ws.Cells(1, 1).End(xlToRight))
            rngCopy.Copy
            wsTally.Cells(1, 1).Value = "Sheet Name"
            wsTally.Cells(1, 2).PasteSpecial xlPasteValues
                
CopyValues:
        
        With ws
               Set rngCopy = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 6)
        End With
        rngCopy.Copy
        With wsTally
            With .Cells(.Rows.Count, 1).End(xlUp).Resize(rngCopy.Rows.Count, 1)
                .Value = ws.Name
                .Offset(0, 1).Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
            End With
        End With
        
NextWs:
Next ws
End Sub
 
Last edited:

Cablelane

New Member
Joined
Mar 16, 2009
Messages
5
Wow! Looks like an awesome solution! :confused: But I'm not sure how to use it. Opened VBA editor, copied and pasted; obviously I'm missing something. Can you give me some more help on writing this code into my workbook please?

Thank you for your help!!
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
First, you have to paste the code into a standard code module. In the VBEditor, from the Project Explorer window (if you can't see that you can display it via the View menu), right-click your workbook and choose Insert > Module.

Paste the code in there.

Secondly, you need some method to run it. You can always just make sure your cursor is somewhere within the code and hit Run from the VBE, but that's a bit clumsy. You can assign macros to Form buttons, shapes etc., or you can have it run on certain workbook events, like every time you save etc....

Decide how/when you want to update your Tally sheet and take it from there.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,079
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top