Have a tuff Formula Challange

[rlleblan]

I am currently having trouble developing a complicated formula. I am new to the forum and have seen some real pros. Hoping someone will provide the answer needed.

What I have:

I have a workbook with around 30 sheets in it all with name tabs. (ie sheet one might be labeled bob and sheet two labeled joe). In column "A" on each sheet I have a various dates populated (ie. 02/15/2013) and in column "B" and "C" the number of hours spent working on project. (direct and indirect)

I want to take any date from any sheets that fall into a range of dates and if criteria is met add the "B" and "C" values producing one total for all sheets.

the result would be for Q1 X amount of hours were spent (X=total hours for all sheets and all cells that met the time range)

Thank you in advance

 

[Brianwarnock]

Note that I am talking pre 2007 although I suspect that it applies to later releases.

I think that this is one of those queries that is easy to handle in Access but not in Excel.
I suspect that the number of rows per sheet is dynamic , which therefore rules out a formula approach.

If the number of rows was fixed then entering the dates in cells on a query sheet and having Sumproduct formulae calculate the results for each sheet and then sum the results could work.
But I think you are going to need code if you wish to avoid changing the formulae frequently , assuming the answer to suspition on dynamic row numbers is correct.

Brian

 

[Brianwarnock]

Decided to write the code in case you want to see what it would be like.
A sheet named query is inserted as the first in the workbook
A! contains the first date and A2 the second the sum is returned in C3, all of this can be changed.

the lastrow calculation may also be different for the megasize worksheetts post 2007 although as long as the number is greater than that which you will reach it doesnt matter, also the first row probably will not be 1 because of headers.

Brian

Sub mysub()

Dim wsht As Worksheets
Dim n As Long
Dim sn As Long
Dim lastrow As Long
Dim r As Long
Dim Total As Long

n = ActiveWorkbook.Worksheets.Count

For sn = 2 To n
    Set ws = Worksheets(sn)
    lastrow = ws.Range("B65536").End(xlUp).Row
    For r = 1 To lastrow
        If ws.Cells(r, "C") >= Worksheets("query").Range("A1") And ws.Cells(r, "C") <= Worksheets("query").Range("A2") Then
   Total = Total + ws.Cells(r, "B")
        End If
    Next r
Next sn

Worksheets("query").Range("C3") = Total

End Sub

 

[rlleblan]

Thank you for responding. What do you mean by the cells are fixed.

On each sheet the A column has dates (one per cell)\

 

[Brianwarnock]

I said number of rows are fixed not cells are fixed. By that I meant that a worksheet always had a fixed number of rows of data, very unlikely as fresh data will be continually added.

Anyway I think the code option is the way to go. I reread your first post and have now created a simple Workbook to show what I mean with the code running off a command button on the query sheet.

You can view the code by right clicking on the query sheet tab.

Brian

 

[NBVC]

You may be able to use the formula method described here:

http://www.xl-central.com/sum-single-criteria-multiple-sheets.html

using SUMIFS instead for multiple criteria.

e.g.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&A2:A32&"'!B:B"),INDIRECT("'"&A2:A32&"'!A:A"),"<="&C2,INDIRECT("'"&A2:A32&"'!A:A"),">="&D2))+SUMPRODUCT(SUMIFS(INDIRECT("'"&A2:A32&"'!C:C"),INDIRECT("'"&A2:A32&"'!A:A"),"<="&C2,INDIRECT("'"&A2:A32&"'!A:A"),">="&D2))+SUMPRODUCT(SUMIFS(INDIRECT("'"&A2:A32&"'!C:C"),INDIRECT("'"&A2:A32&"'!A:A"),"<="&C2,INDIRECT("'"&A2:A32&"'!A:A"),">="&D2))

This assumes the names of the sheets are in A2:A32 (you can create a named range for this, if desired).

This also assumes your start/end dates range are in C2 and D2, respectively, of your summary sheet.