Workbook with multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1588033%22%20slang%3D%22en-US%22%3EWorkbook%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588033%22%20slang%3D%22en-US%22%3EI%20have%20a%20workbook%20template%20for%20each%20day%20in%20a%20month.%20I%20need%20to%20tally%20number%20in%20violation%20column%20at%20bottom%20of%20each%20day%E2%80%99s%20sheet.%3CBR%20%2F%3EMy%20problem%20is%20i%20need%20to%20separate%20the%20tally%20by%20either%20%E2%80%9CS%E2%80%9D%20or%20%E2%80%9CL%E2%80%9D.%3CBR%20%2F%3ESo%20the%20%E2%80%9CS%2FL%E2%80%9D%20column%20then%20the%20violation%20column%20is%20a%20number.%3CBR%20%2F%3EIf%20it%20is%20an%E2%80%9DS%E2%80%9D%20with%202%20violations%20I%20would%20like%20the%20total%20%E2%80%9CS%E2%80%9D%20violations%20in%20one%20cell.%20I%20would%20like%20total%20%E2%80%9CL%E2%80%9D%20violations%20in%20another%20cell.%3CBR%20%2F%3ECan%20this%20be%20done%20using%20an%E2%80%9DIF%E2%80%9D%20statement%20or%20do%20I%20have%20to%20two%20different%20cells%2C%20one%20for%20S%20and%20one%20for%20L%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1588033%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588169%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759071%22%20target%3D%22_blank%22%3E%40Amass219%3C%2FA%3E%26nbsp%3B%20So%20for%20the%20question%20%22%3CSPAN%3ECan%20this%20be%20done%20...%20or%20do%20I%20have%20to%20two%20different%20cells%2C%20one%20for%20S%20and%20one%20for%20L%3F%22%20that%20answer%20is%20Yes%20it%20can%20be%20done%20without%20separating%20them.%26nbsp%3B%20However%2C%20it%20isn't%20%22pretty%22.%26nbsp%3B%20I%20don't%20have%20a%20single%20formula%20to%20sum%20across%20columns%20on%20multiple%20sheets%20but%20it%20can%20be%20done%20by%20adding%20some%20named%20ranges.%26nbsp%3B%20That%20said%20there%20are%20easier%20solutions.%26nbsp%3B%20For%20example%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EA)%20You%20may%20want%20subtotals%20for%20each%20day.%26nbsp%3B%20So%20lets%20say%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ecolumn%20J%20has%20the%20labels%20S%2FL%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ecolumn%20K%20has%20the%20count%20for%20each%20S%2FL%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20M1%20on%20each%20sheet%20(each%20day)%20will%20be%20subtotal%20for%20S%20then%20M1%3A%3C%2FSPAN%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIF(J%3AJ%2C%22s%22%2CK%3AK)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20M2%20can%20be%20the%20subtotal%20for%20L%20each%20day%20and%20use%20the%20same%20formula%20but%20with%20an%20%22l%22%20instead%20of%20an%20%22s%22%3C%2FP%3E%3CP%3EThen%20on%20the%20Monthly%20Summary%20sheet%20you%20can%20just%20have%20a%20cell%20for%20the%20Total%20S%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM('Day%201%3ADay%2031'!M1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWhere%20%22Day%201%22%20is%20the%20name%20of%20the%201st%20sheet%20and%20%22Day%2031%22%20is%20the%20name%20of%20the%20last%20sheet%20to%20tally.%3C%2FP%3E%3CP%3EThe%20same%20thing%20but%20with%20M2%20instead%20of%20M1%20for%20L%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%20B)%20A%20(possibly)%20even%20better%20solution%20would%20be%20to%20re-evaluate%20how%20you%20are%20entering%20the%20data%20(and%20why).%26nbsp%3B%20Why%20are%20you%20entering%20data%20on%20different%20sheets%20for%20each%20day%3F%26nbsp%3B%20Why%20not%20have%20a%20single%20running%20list%20of%20all%20the%20data%20on%20a%20single%20master%20table%3F%26nbsp%3B%20Then%20if%20you%20want%20a%20particular%20layout%20for%20a%20single%20day%20you%20have%20a%20second%20sheet%20with%20a%20pivot%20table%20or%20just%20lookup%20values%20that%20point%20to%20that%20master%20table.%26nbsp%3B%20You%20could%20have%20a%20single%20sheet%20with%20a%20cell%20for%20DAY%20and%20all%20the%20lookup%20values%20would%20query%20based%20on%20that%20DAY%20or%20you%20could%20if%20you%20insist%20have%2031%20sheets%20to%20break%20it%20into%20each%20day.%3C%2FP%3E%3CP%3EHaving%20this%20single%20master%20table%20for%20your%20data%20entry%20and%20a%20separate%20tab%20for%20your%20data%20format%2Foutput%20gives%20you%20a%20lot%20of%20flexibility%20in%20analyzing%20and%20modifying%20output%20formats%20with%20much%20less%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ec)%20if%20you%20insist%20on%20keeping%20it%20the%20way%20you%20have%20it%2C%20here%20is%20a%20reference%20to%20how%20to%20do%20SUMIFS%20across%20multiple%20sheets%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.got-it.ai%2Fsolutions%2Fexcel-chat%2Fexcel-tutorial%2Fsumif%2Fsumif-across-multiple-sheets%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.got-it.ai%2Fsolutions%2Fexcel-chat%2Fexcel-tutorial%2Fsumif%2Fsumif-across-multiple-sheets%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1597175%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1597175%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E.%20Thank%20you%20for%20taking%20the%20time.%20Each%20day%20sheet%20is%20used%20by%20management%20as%20a%20log%20of%20my%20daily%20activity%20which%20is%20cross%20referenced%20with%20gps.%3CBR%20%2F%3EThe%20monthly%20sheet%20is%20a%20tally%20sheet%20used%20to%20see%20my%20total%20activity%20for%20the%20month.%20The%20layout%20was%20setup%20years%20ago.%20I%E2%80%99m%20trying%20to%20automate%20the%20tally%20as%20much%20as%20possible.%3CBR%20%2F%3EThank%20you%20again%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1597508%22%20slang%3D%22en-US%22%3ERe%3A%20Workbook%20with%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1597508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759071%22%20target%3D%22_blank%22%3E%40Amass219%3C%2FA%3E%26nbsp%3B%20After%20re-reading%20your%20original%20post%20now%20I%20think%20my%20(a)%20solution%20is%20exactly%20what%20you%20want.%26nbsp%3B%20But%20excel%20is%20so%20versatile%20and%20everyone%20does%20something%20different%2C%20us%20volunteers%20here%20often%20have%20a%20hard%20time%20understanding%20exactly%20what%20you%20are%20trying%20to%20do%20without%20an%20example%20sheet%20explicitly%20showing%20us%20the%20format%20and%20what%20you%20want.%26nbsp%3B%20The%20more%20info%20we%20have%20the%20more%20we%20can%20help.%26nbsp%3B%20If%20you%20can't%20post%20the%20actual%20workbook%20then%20maybe%20just%20strip%20out%20the%20personal%2Fconfidential%20info%20and%20then%20post%20that.%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
I have a workbook template for each day in a month. I need to tally number in violation column at bottom of each day’s sheet.
My problem is i need to separate the tally by either “S” or “L”.
So the “S/L” column then the violation column is a number.
If it is an”S” with 2 violations I would like the total “S” violations in one cell. I would like total “L” violations in another cell.
Can this be done using an”IF” statement or do I have to two different cells, one for S and one for L?
3 Replies

@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:

=SUMIF(J:J,"s",K:K)

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:

 https://www.got-it.ai/solutions/excel-chat/excel-tutorial/sumif/sumif-across-multiple-sheets

 

 

 

@mtarler. Thank you for taking the time. Each day sheet is used by management as a log of my daily activity which is cross referenced with gps.
The monthly sheet is a tally sheet used to see my total activity for the month. The layout was setup years ago. I’m trying to automate the tally as much as possible.
Thank you again

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