Forum Discussion
Count across other files
Let me suggest that--if it's something you're in a position to do--you create a single database rather than separate monthly ones. Just add a column to represent the month. Once you've done that, a Pivot Table will do all the work for you. See the attached.
(Note: to make it easier to sum the activities, I used a 1 instead of "Y" to indicate activity. But you could also let the Pivot Table just count by entering only the "Y" when a person was active in a given month; by omission, the blank would mean 'N" and the blank would not be counted. If you wanted to get more granular (and thereby more substantive or meaningful), you could record every date the person was active and count those by month)
Thanks all for your help so far!
I've had additional issues with @sumproduct not working on other laptops, I think due to different versions so have rewritten the spreadsheet to make it more reliable.
Issue still remains with how to count activity / attendance over the last 6 months period.
I've created an additional table below showing activity be members over the months. A '1' indicates that the member has attended at least once in the month.
The specific question I need to answer is "How many members have been actively attending the pantry in the last 6 months?"
I don't need to count how many visits per member, just if a member has attended any time within the last 6 months then that will count as a "1".
So for March the answer would be "2".
For April the answer would be "4"
For May the answer would be "6"
The awkward part is the table will need to cover one year and so will need to include the previous 6 months data, which I can manually copy over from the previous years file.
Thanks!
Attendance Member Number Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 1 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 2 0
0 0 0 0 0
0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 3 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 4 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 6 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 I don't see a connection between the data you show in that matrix and the question you ask.
Is it possible for you to post an actual spreadsheet rather than just text in a posting? If you can't attach it directly as I did in my earlier response to you, then post it on OneDrive or GoogleDrive and paste a link here that grants access.
Sticking to the table, let's say I'm interested in calculating which members have been active in April and the preceding 5 months. I'm not interested in how many for each member just if a member has been active at all.
So in the table for the month of April:
Member 1 has been active in the last 6 months
Member 2 has been active in the last 6 months
Member 3 has been active in the last 6 months
Member 4 has been active in the last 6 months
Member 5 has not been active in the last 6 months
Member 6 has not been active in the last 6 months
The answer is 4.
How can I calculate that?
Thanks
