Forum Discussion
Count across other files
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
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.
But how does that set of descriptions of each member's activity (or lack thereof) connect with that prior table? This one!
And that still isn't an actual spreadsheet.
I think we're talking past each other; not sure why. I am also on vacation now so not checking except sporadically. But I'd really like to get a clear description of how you store your data and the questions that need answering, and have the former in the form of a spreadsheet, on OneDrive or GoogleDrive.
- Thanks all for the responses.
This 'system' is going to be used by people with limited experience of spreadheet programs and limited experience of directory structures so I've gone away from a monthly file system to having everything in one file. I want to prepare that file for the current year and send it out to the person who's typing the daily attendance data in.
The file also contains personal data so I can't share it here or link from OneDrive. There's also a lot of other things going on in the file so I can't pick and choose tabs to share as it will just break. This is why I'm creating example tables on here so people can help me grasp the concept which I can take away and apply to the real file.
Thanks.
select * from consolidateSheet where f04 like 'Y';
select f03,group_concat(f02) from consolidateSheet where f04 like 'Y' group by f03 having(count(f03)>2);
f01 f02 f03 f04 f05 f06 f07 f08 f09 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25
Count Member2.xlsx Jan 1 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Jan 3 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Feb 3 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Feb 4 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Feb 5 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Mar 3 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Mar 4 Y - - - - - - - - - - - - - - - - - - - - - Count Member2.xlsx Mar 5 Y - - - - - - - - - - - - - - - - - - - - - f03 group_concat(f02)
3 Jan,Feb,Mar - Previously you mentioned that excel workbook has the month wise sheet but it seems you have data in the single sheet. so you want to count in single sheet?
