# multiple conditional sum? or average?

#### Cablelane

##### New Member
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-comfficeffice" /><o></o>
<o> </o>
Solutions I’ve tried: <o></o>
1) pivot table: worked well on individual sheets, but couldn’t get it to calculate across all sheets, based on month.<o></o>
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></o>

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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:
Wow! Looks like an awesome solution! 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?

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.

Replies
9
Views
309
Replies
0
Views
152
Replies
5
Views
175
Replies
4
Views
208
Replies
6
Views
311

1,211,452
Messages
6,101,937
Members
447,764
Latest member
gopalgriffith

### 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.

### Which adblocker are you using?

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

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