I have a workbook that I am tracking the attendance of each agencies have people showing up. whetehr it is day or night. I have to report weekly, how many on day and night from each agency is showing up daily and nightly. I am tracking roughly about 18 different agencies and 100-150 people. I am not versed in formulas and currently I am just counting them up for each day and entering how many showed up day and how many showed up night. please help!

lageared Can you please upload (without any real names or other identifiable info) a copy of your spreadsheet. It should be a fairly simple thing to summarize the data if what you keep has something like the following layout. A series of columns that could look like this (or some variation)Date Agency AM/PM Count (that's four columns) It could be more granular if you get reports that are more detailed. For example, if people are showing up for different purposes; if some of the people you're counting are volunteers, others are people being served...you might want to have different columns reflecting that Summarizing data like this is one of Excel's strong suits...you just need to collect the data in an orderly (systematic) way. I'm sure you'll find people here willing and eager to help. You can help us help you by giving a bit more details and, ideally, a sample of the workbook you've already created.

mathetesI HAVE ATTACHED A SAMPLE WITH ALL THE NAMES AND ETC REMOVED.

lageared Oh, my. There's so much I'd want to change here... That aside, I need to ask you a few questions.First, what do your weekly reports look like.Are they simply high level counts by agency? (BY high level count, I mean nothing but numbers, breaking out Day vs Night, but no backup details such as names, or how many times a given person was counted.)What sort of format do you use for those weekly reports?Is there an overall summary, or do you only report at the individual agency level. So those questions had to do with the end product, what you might call the "output" of your spreadsheet. At the other end of things:How do you get your input from these agencies? Since you appear to track by name, is there a sign-in sheet that gets filled in, and then you (or somebody) sits and transcribes those attendance sheets into this series of worksheets?Do you get that input daily, or at the end of the week? To what extent would you be willing to radically change how this is laid out? The reason I ask is that it actually would be quite easy to produce weekly reports for each agency as well as an overall summary if instead of 15 separate (but identical) pages, you had 1 database in which you just recorded for each person who attended, for each day (if someone didn't attend, there'd be no need to record anything)...DateAgency IDName or personal IDD or N (four pieces of info only, yet it could produce all those summary reports by week, by month, by quarter...whatever) and there'd be no need to go back and forth from one sheet to another, no searching to find the right name and make sure you marked them correctly. We could design a form that prompts for the name of the agency, and then prompts for the names of people who are on that agency's list (so no need to type it every time). In short it could be made to work with a minimum of hassle at the data collection end....

matheteslet me give you a little background. I have been deployed on a special long term assignment involving multiple Agencies for handling an operation for COVID 19. Those agencies send people to be deployed, either during the day and/or people during the night. My boss wants me to include how many people we have deployed from each agency total and how many each agency is sending for the day assignment as well as the night assignment on a weekly staffing report that we are using in onenote as an outline type form which has numerous other items that have to be reported not having to do with staffing. There are 2 of us that are collaborating on the report I handle all the staffing for this assignment he is handling all the operations of this assignment. I am not versed in excel I kind have been thrown into this assignment. What I put together was all I knew on how to use excel. I have been on this assignment since the beginning of March. My boss wants this information for arguments to his boss for justification on the numbers we used for budgetary and training needs. so its not real in depth but so I don't have to count each individual for the day and then count each individual for the night and then go through each sheet and add them all up. The people sign an accountability sheet when they check in and when they check out. The things I need to track is numbers of people as a whole, from each agency, and see any trends of personnel diminishing, which would necessitate me to contact other agencies to be deployed if my numbers diminish. If any of that makes sense. I think in paragraph form I answered everything?I am completely new to all of this

lageared A short Postscript to what I wrote above. I thought it might benefit you to see another way to collect the data, and then how easily the Pivot Table could summarize it. There are other ways as well, maybe even better, to report on it. Either way, the heart of it is the single database. As an aside, not to fault you for collecting the information the way you do, but you've in essence fallen prey to the very understandable tendency to use Excel as a "neat" way to automate what you might have done on paper...and you can print these sheets out, if somebody wanted to see the back up, they'd be all neat and orderly. But you're in essence taking the green ledger paper paradigm and just using Excel as a modern way to do that. Excel is wonderful for summarizing databases....but you need to start off with a great database design in order to let it do the heavy lifting. I hope this illustrates what I mean.

I need to count how many times 2 different letters appear in a column & totals in diff. cells

19 Replies

mathetes
Silver Contributor
Apr 15, 2020
lageared
Can you please upload (without any real names or other identifiable info) a copy of your spreadsheet.

It should be a fairly simple thing to summarize the data if what you keep has something like the following layout. A series of columns that could look like this (or some variation)
Date Agency AM/PM Count (that's four columns)

It could be more granular if you get reports that are more detailed. For example, if people are showing up for different purposes; if some of the people you're counting are volunteers, others are people being served...you might want to have different columns reflecting that

Summarizing data like this is one of Excel's strong suits...you just need to collect the data in an orderly (systematic) way.

I'm sure you'll find people here willing and eager to help. You can help us help you by giving a bit more details and, ideally, a sample of the workbook you've already created.
- lageared
  Copper Contributor
  Apr 15, 2020
  mathetesI HAVE ATTACHED A SAMPLE WITH ALL THE NAMES AND ETC REMOVED.
  APRIL DEPLOYMENT HISTORY SAMPLE.xlsx73 KB
  - mathetes
    Silver Contributor
    Apr 15, 2020
    lageared
    
    A short Postscript to what I wrote above. I thought it might benefit you to see another way to collect the data, and then how easily the Pivot Table could summarize it. There are other ways as well, maybe even better, to report on it. Either way, the heart of it is the single database.
    
    As an aside, not to fault you for collecting the information the way you do, but you've in essence fallen prey to the very understandable tendency to use Excel as a "neat" way to automate what you might have done on paper...and you can print these sheets out, if somebody wanted to see the back up, they'd be all neat and orderly. But you're in essence taking the green ledger paper paradigm and just using Excel as a modern way to do that.
    
    Excel is wonderful for summarizing databases....but you need to start off with a great database design in order to let it do the heavy lifting. I hope this illustrates what I mean.
    
    APRIL DEPLOYMENT Pivot.xlsx80 KB

Forum Discussion

I need to count how many times 2 different letters appear in a column & totals in diff. cells

19 Replies

Resources