Aug 13 2020 03:51 PM
Aug 13 2020 03:51 PM
Aug 13 2020 05:45 PM
@Amass219 So for the question "Can this be done ... or do I have to two different cells, one for S and one for L?" that answer is Yes it can be done without separating them. However, it isn't "pretty". I don't have a single formula to sum across columns on multiple sheets but it can be done by adding some named ranges. That said there are easier solutions. For example
A) You may want subtotals for each day. So lets say:
column J has the labels S/L
column K has the count for each S/L
and M1 on each sheet (each day) will be subtotal for S then M1:
and M2 can be the subtotal for L each day and use the same formula but with an "l" instead of an "s"
Then on the Monthly Summary sheet you can just have a cell for the Total S:
=SUM('Day 1:Day 31'!M1)
Where "Day 1" is the name of the 1st sheet and "Day 31" is the name of the last sheet to tally.
The same thing but with M2 instead of M1 for L
or B) A (possibly) even better solution would be to re-evaluate how you are entering the data (and why). Why are you entering data on different sheets for each day? Why not have a single running list of all the data on a single master table? Then if you want a particular layout for a single day you have a second sheet with a pivot table or just lookup values that point to that master table. You could have a single sheet with a cell for DAY and all the lookup values would query based on that DAY or you could if you insist have 31 sheets to break it into each day.
Having this single master table for your data entry and a separate tab for your data format/output gives you a lot of flexibility in analyzing and modifying output formats with much less work.
c) if you insist on keeping it the way you have it, here is a reference to how to do SUMIFS across multiple sheets:
Aug 18 2020 12:51 PM
Aug 18 2020 03:01 PM
@Amass219 After re-reading your original post now I think my (a) solution is exactly what you want. But excel is so versatile and everyone does something different, us volunteers here often have a hard time understanding exactly what you are trying to do without an example sheet explicitly showing us the format and what you want. The more info we have the more we can help. If you can't post the actual workbook then maybe just strip out the personal/confidential info and then post that.