number of dates entered for one month

OshaK

we have a weekly event that runs throughout the year, sometimes it could be 3 per month, 4, or even 2 (depending on the holidays etc).

People need to check their attendance so they select the date in the form and submit it, so the same date appears on multiple rows in the sheet.

How can I calculate just the total number of events per month (4, 3 or 2) regardless of how many people checked in and how many rows have the same date in them? in a different sheet.

thank you!

Matthew L

Hi Osha,

You can use =MONTH(Date@row) to extract the month from the dates on the submission form.

Then you can use a report to group the entries by month and use the summary filter to count the total number of submissions from each month.

OshaK

@Matthew L thank you. I need it for another sheet, not report, because i'm calculating average attendance for several demographic groups per month. I added the number manually to the first couple of months, but it would be better if it can get calculated. From my understanding, the summary can't be inserted into a cell in the different sheet.

Is there a formula that i can add to the other sheet cell to calculate the number?

thank you!

Matthew L

If you make this month formula a column formula called Month, you could use this formula in your other sheet.

=COUNTIF({Month}, 10)

So by referencing the Month column from your source sheet you can use this formula to count the number of values for the 10th month, so in this example October.

If you need September you would just change that last value to 9, August = 8, etc.

OshaK

@Matthew L thank you. I tried that first, but this formula counts all the instances entered, so I get 87 for October, because 87 people registered and "10" appears on 87 rows. I have another column where they have to select the actual dates, so even though there are 87 "10" instances, there are only 4 dates for October. How can i count just the unique dates? maybe it's easier to add them manually but I wanted to automate as much as possible. thank you.

Matthew L

@OshaK, got it! I missed the unique value aspect of this. You can use the DISTINCT function along with Collect to find the unique dates in your range. Your Final formula should look something like this, which may vary based on the column names you are using and the month you are looking for.

=COUNT(DISTINCT(COLLECT({Submission Date}, {Month}, = 10)))

OshaK

@Matthew L. thank you. this formula is doing something, but still doesn't returned the correct number.

Here is the formula I used:

=COUNT(DISTINCT(COLLECT({date}, {{months}, =10))) . It returns "1" ("4"s below are manual entries).

here is the different sheet with the references:

"date" refers to the "Date (Helper Column)" and "Months" to "Months (Helper Column)". (for some reason, I can't make a screenshot and retains the reference names in "Sheet Reference Name" - it gets stripped when i try to screenshot it but they are there.

What am I doing wrong?