Log In
Register
Home
New Member
April 14, 2020
Offline
Hi,
i hope everybody is well.
I am wracking my brains trying to sum values from multiple sheets based on date. The workbook has hundreds of sheets with several sheets per date (day) all with the same format, including the summary sheet. I have used index and indirect to sum common data into a summary sheet, but need to summarize the data from a single cell by date. I have tried using the formula
| =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!A:A"),C2,INDIRECT("'"&SheetNames"'!B:B"))) |
|
This is not working, I am guessing, because I am only looking for information from single cells and not a range. The date ending cell is C2 and the cell that I want to sum is A5. Also, the date ending can be manually changed by a SUMIF formula based on the week number input on the summary sheet. Could someone please lead me in the right direction. Thanks in advance! P.S. in this case I want to avoid power query and pivot tables. I also have no experience with VBA
|
Trusted Members
December 20, 2019
Offline
Hi Michelle
Could you add a smallish sample workbook so we dont have to recreate the problem?
Purfleet
July 16, 2010
Offline
Hi Michelle,
What Purfleet said. In the meantime, have you tried putting in proper cell references instead of whole column references? If you want to sum cell A5, then your SUMIF arguments are back to front. Remember the syntax is:
=SUMIF(range, criteria, sum_range)
Your formula is summing column B, but there aren't any values in column B.
Here is the tutorial on 3D SUMIF to refresh your memory on how to write this formula. That said, I'd use Power Query over this every time.
Mynda
New Member
April 14, 2020
Offline
Hello,
Thank you both!
I would love to use power query - it is a very useful tool. The challenge is that the user of this book would would not find that user friendly and needs the sheets to be formatted as you will see it. The current means is to use 12 books and I am trying to consolidate into one which would required 900+ sheets. I would like to be able to summarize the multiple sheers per day into a summary sheet that would calculate a sum (i.e. A5) based on date ending (i.e. C2), so that when the user inputs the week-ending date the sum would automatically update. I have attached a mock-up of the workbook.
Thanks!
Michelle
Trusted Members
December 20, 2019
Offline
Hundreds of sheets in a workbook is some going, I usually tell people to stop at 20.
So you want to sum A5 in every sheet where the date in B2 is between the dates in Weekly summary? Does this do what you want? Not sure how long it would take to run on hundreds of sheets.
There is an exclusion list of sheets we don’t want to sum which might need changing
You could try and copy this module into the large workbook and then run it to see what happens (don’t forget to back up first!)
Sub SumAllSheets()
Dim sDate As Date
Dim eDate As Date
Dim tDate As Date
Dim Boxes As Long
Dim ws As Worksheet
sDate = Worksheets("Weekly Summary").Range("c1")
eDate = Worksheets("Weekly Summary").Range("c2")
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "A", "Blank Report", "B", "FM", "Weekly Summary", "Sheet2", "LIST", "Summary", "Riken Summary", "Food Industry Summary"
'Debug.Print ws.Name
GoTo Skip
Case Else
End Select
' If ws.Name = "A" Then
' GoTo Skip
' End If
tDate = ws.Range("b2")
If tDate >= sDate And tDate <= eDate Then
Boxes = Boxes + ws.Range("a5")
End If
Worksheets("Weekly Summary").Range("a5") = Boxes
'Debug.Print sDate & " " & eDate & " " & tDate & " " & ws.Name
'
'Debug.Print Boxes
Skip:
Next ws
End Sub
1 Guest(s)
