Summary using sumif

[Karen N.]

Hi,

I'm trying to build a summary sheet in a workbook that contains 30
worksheets - 6 sheets are inconsequential, 23 are time tracking sheets for
individual projects, and 1 is the summary sheet. I want the summary sheet to
list every day in Column A (e.g., 11/1, 11/2, etc.), and Column B to house
the formula. I'm trying to get the formula to find the date in Summary
Column A by looking in Column A of each of the 23 sheets, and if it matches,
I want it to sum the total in Column Q.

The project time tracking sheets each have project info in the first 8 rows,
the column labels are in row 9 and then throughout the remainder of the
sheet. Time tracking is separated by a blank row followed by a new set of
column labels to start the next month. Column A = Date, Column B = project
status, Columns C-P = start/stop times, Column Q = time sum for each row

In other words, I'd like a formula (no macros, add-ins, etc.) that can
perform the following:
=SUMIF('ID 138-8:ID 1089-0'!A:A,Summary!A2,'ID 138-8:ID 1089-0'!Q:Q)
ID 138-8 = first time tracking sheet
ID 1089-0 = last time tracking sheet
Summary = summary sheet

I've spent hours trying to use consolidate, vlookup, if, dsum, etc. formulas
to no avail, and I'm just not technically savvy enough to build a
multi-functional, nested formula. Any assistance in building a 'simple'
formula is greatly appreciated!

 

[Otto Moehrbach]

Karen
That's a mouthful. You say the time tracking sheets have dates in
Column A with a blank row between months. See if I understand you. Excel
will look at a date in Column A of the Summary sheet. Let's call that
TheDate for now. Excel will then look at each time tracking sheet and
search Column A for TheDate. If it finds TheDate, you want Excel to sum ALL
of Column Q in that sheet. Then you want Excel to look for TheDate (the
same TheDate) in the rest of the time tracking sheets and, if found, you
want those Column Qs summed as well? Do you want all those summations added
together? And what have you got against macros? <g> Otto

 

[Karen N.]

Good morning Otto,

Yes, it was a lot - I wanted to make sure I covered all pertinent
information, but I tend to over-explain

Yes, I want all of the summations added together for each TheDate. I don't
have anything against macros, except I have absolutely no idea how to use
them! Is a macro be the solution to my issue?

Thanks and have a great day!

 

[Otto Moehrbach]

Yes, I think the only way to go is with VBA. I'll work up some code
(macros) and get back to you. Otto

 

[Otto Moehrbach]

Karen
In writing the code, I have to include some way for Excel to
differentiate between what sheets to work with and what sheets to ignore.
The "Summary" sheet is easy. I presume it is named "Summary"
You said you have 6 "inconsequential" sheets and then you have 23 project
time tracking sheets. Think of that as being 2 groups of sheets. I need to
be able to distinguish any given sheet as belonging to one group or the
other. If I can identify all the sheets of one group, I will not need to
identify sheets of the other group. They will simply be "the rest of the
sheets".
Can you think of any characteristic that is common to all the sheets of one
group that is not present in any of the sheets of the other group? Things
like:
The first character of the sheet name is a number.
The entry in cell XX is "Doodle".
The length of the sheet name is always X or always more/less than X.
I can always simply use the sheet names but it would simplify the code if
you know of any way to differentiate the 2 groups of sheets. Otto

 

[Karen N.]

All of the time tracking sheets are named as "number,dash,number" - e.g.,
138-8 or 1067-13; whereas the inconsequential sheets are named with either
all text or "text number" - e.g., WAPG 2008. Does this qualify? If not, I
can move the inconsequential sheets to a new workbook so this one only
contains the time trackers and summary.

 

[Otto Moehrbach]

That's good. I'll simply look at the first character in the sheet name and
if it's numeric, that's it. Thanks. Otto

 

[Otto Moehrbach]

Karen
You say that you want Column A of the "Summary" to list the dates. Do
you mean you want me write the code (macros) to list all the dates or will
you list them? What will those dates look like? From what date to what
date? Otto

 

[Karen N.]

The date in column A is in MM/DD/YY format - e.g., 05/18/08. I anticipated
inputting the first date (05/01/08) and formula, and then using the
click-and-drag-down thing to fill in the rest of the dates and formulas. The
first date will be 05/01/08 and will go to 01/31/09.

I don't know how this will affect the code... please do whichever is easiest
for you

 

[Otto Moehrbach]

Karen

Here is the macro. You said that you don't know anything about
macros so I think I will just confuse you if I explained how and where to
put the macro and how to run it. If you wish, send me an email with your
file attached and I'll place the macro where it belongs and I'll place a
button in the Summary sheet that you can click to run the macro whenever you
want to. If your data is proprietary just fake the data. If you're
comfortable with deleting sheets and replacing sheets, simply make a copy of
your file and delete all the proprietary sheets/data. Then when I send it
back you can replace everything. Or you can just send me an email and I'll
send you the small file I used to develop the code and you can take it from
there. My email address is (e-mail address removed). Remove the
"extra" from this address. Otto

Sub GetSum()
Dim ws As Worksheet, rSumColA As Range
Dim PTTColA As Range, PTTColQ As Range
Dim i As Range, SumQ As Double
Application.ScreenUpdating = False
Sheets("Summary").Select
Set rSumColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each i In rSumColA
SumQ = 0
For Each ws In ThisWorkbook.Worksheets
If IsNumeric(Left(ws.Name, 1)) Then
With ws
Set PTTColA = .Range("A10", .Range("A" &
Rows.Count).End(xlUp))
If Not PTTColA.Find(What:=i.Value, LookAt:=xlWhole) Is
Nothing Then
Set PTTColQ = .Range("Q10", .Range("Q" &
Rows.Count).End(xlUp))
SumQ = SumQ + Application.Sum(PTTColQ)
End If
End With
End If
Next ws
If SumQ > 0 Then _
i.Offset(, 1).Value = SumQ
Next i
Application.ScreenUpdating = True
End Sub