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

[lageared]

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!

[#2]

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

[#3]

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

[#4]

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

• Date
• Agency ID
• Name or personal ID
• D 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....

[#5]

@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  

[#6]

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

[#7]

@lageared 

I wondered if it was Covid related...thank you for doing it! Anyway, I'd be happy to help you design something a bit more robust--our messages passed each other a few minutes ago. Take a look at the first sheet now in what I sent back. I just created a few "dummy day" records...and if you play around with adding new rows at the bottom you'll see two things
I created drop down boxes for three of the four columns...so there's only a need to select an answer, not to type it. The only typing per se is the date, and you can copy it down multiple rows after you've entered the other info from the attendance sheet.

Once you've entered new rows of data, if you go to the Data toolbar, you'll see a place there where you can click on "Refresh All" and the pivot table will automatically update with all the new info.

It's not designed right now to report by week, but that's a simple change. I wanted to get this to you just as a demo of what I was saying a simple but unified database could look like.

[#8]

Exactly, I know it is what I was doing but I felt so stupid because I know Excel could be so much more beneficial if I just knew how to do it.

[#9]

@lageared 

We all begin somewhere, some time. I happen to have begun working with databases and arrays back in the early 1970s....and have been an Excel junkie ever since it came out. Retired in 2002 (I'm 78 yrs old) and now am having fun answering questions like yours here on this message board.

Anyway, if what I sent looks useful, I'd be happy to work to make it a bit more elegant.... and even personalize it for you.

[#10]

Well thank you for helping me because I have been trying to figure this out for 2 days now! I think I understand it. Not 100% sure if I do I did add a name and date and day or night. The question I have is does that list just keep going? Just curious if I open another workbook or sheet for each month? Currently I have the same workbook exactly like the one I sent you but for March. We have been informed we will be on this assignment at least until June but most likely until September! The other question I have is I am using another workbook for tracking anyone that is deployed and becomes sick I have to identify who they were with so I can put all them on quarantine. That is another thing I have been working on but didn't know if it can all be incorporated in one. I really want to thank you for helping me.

[#11]

@lageared 

I'm actually not expecting you to understand all that I've done here. But yes, to your question, the list will just keep going. That's the thing about a database. And NO, you won't need to add a new sheet for a new month. That's another thing about a database. The reporting can be tailored to whatever timeframe you wish.

So I've been playing around a bit myself as you'll see on the attached. I've gone away from the Pivot Table to the newest technology that Microsoft has introduced just in the last year...ARRAY formulas. Just been learning them over the last week or two.

I added a column here to make it easier to select a week--this column determines a week number (e.g. the 14th week of the year)--and does it automatically based on the date you enter. Then to create a report, you just enter the start date of the week (I'm assuming Sundays for that)....and it automatically adjusts for that week.

And as you add rows to the database....it captures them and reports them for the corresponding week.

Now: I can help you create a drop down list of names for each center and then, for each center, a specific list of names of the people who are deployed in that center. You could update it from there, so it always was current.

Why don't you email me a list of the actual names of your Agencies....I'll create dummy lists for you to complete of the actual names of the real people....

As for the quarantine cases, it probably makes sense to track those on a separate sheet. It could be part of the same workbook, just so all of your records are in the same place.

And you'll be off and running for the next week and the rest of the year.

[#12]

@mathetesI can attach the names of the agencies or just a list of them or I can send you a copy of the workbook I am using which each tab has the agency name and the agency people that have been sent so far for deployment it is only last names used if there are the same last name there is just first initial.  the tab with command is the staff in the operations center.

I don't know if that is ok? 

I have been playing around with the book you have sent me and I know it can be done but not sure how, but can there be like more freedom as to the fact that it will auto complete items such as year and etc.?   

Please let me know

Thank you,

I think this will be awesome!!!

[#13]

these are the Agency names:
Downstate
Eastern
Fishkill
Green Haven
Otisville
Shawangunk
Sullivan
Ulster
Wallkill
Woodbourne
Auburn
Cayuga
Elmira
5 Points
Mohawk
Coxsackie
Oneida
Southport
Command

[#14]

@lageared 

New York!

My home is in Manhattan. Are you National Guard?

OK...I'll get started.

[#15]

Fire Response Protection detail for Correctional Facilities

[#16]

@lageared 

Here you go. I've included a few instructional boxes, and certainly stand ready to help with any questions.

I'll send a private message as well which you'll be able to get through that little envelope icon in the upper right of this screen.

[#17]

@lageared 

It occurred to me that it might be a lot more functional for an attendance tracker if the names of the people were in alphabetical order by last names, and fortunately that kind of thing is very easy. So this version works that way.

I'm sure you'll also want to revise the report format, or add additional capabilities--e.g., showing two, three or even four weeks in succession. That can be done. Let's do a few revisions along those lines before you start filling in the actual names. You can plan to start using this for your May tallies, at the very latest. If we work fast, maybe by this coming week.

[#18]

That would be cool also! I just sent you a private message also.

[#19]

@lageared 

You guys are having great fun - so I won't interfere!

But I note that you said you sent a private message. Can you tell me how to do that please? many thanks,

[#20]

@peteryac60 

To send a private message, you click on the person's name at the top of one of a posting. Doing that takes you to the person's "Profile" page, and up at the top right corner you'll see a "Message" block.