How do I count if someone attended?

fennerb

Hey!

I've been messing with this formula for a bit, and I'm kinda stumped. I'm trying to count how many people have attended a course on a certain day. So, if "Attended" is checked on 1/23, then that should count as one person attending.

Columns Referenced:

Attended = Check

Date = Date format

=COUNTIFS(ISBOOLEAN({Attended}@cell), 1) = true), AND(IFERROR(ISBLANK({Attended}@cell), 0) = false), AND(IFERROR(YEAR(@cell), 0) = 2023), AND(IFERROR(MONTH(@cell), 0) = 1,) AND(IFERROR(DAY(@cell), 0) = 23))

Many thanks to anyone who even took time to read this! :)

Matt Johnson

Hi @fennerb

I would create a table for the event dates and reference that in the formula. Here's an example:

=COUNTIFS(Attended:Attended, 1, [Attended Date]:[Attended Date], [Date of Events]@row)@fennerb

I hope that helps but I might be missing something.

Matt

fennerb

Hey, @Matt Johnson!

Close! I'm going off if Attended is checked and counting by the Training Date.

So, say that 52 people signed up for training on 1/23, but only 48 people actually attended on that day. I'm looking to see how many people were there for that day. So, both "Attended" would have to be checked for the day of 1/23. I hope that made sense. D:

I appreciate you being so quick!

Andrée Starå

Hi @fennerb

I hope you're well and safe!

How do you register who signed up?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

fennerb

@Andrée Starå

Hello!

People register via a form, and after the day is over, I verify that they've attended for that day.

Matt Johnson

Hi @fennerb

Do all events feed to one sheet? And where do you want the count to populate? Switching the words "Attended Date" with "Training Date" in my formula would work but I think we need a little more info on the overall goal.

If you want to compare that number against the count of registered people, then I would do that in a separate formula to keep it easy. Although you could combine and do it like, still with the table on a separate sheet...

=COUNTIFS(Attended:Attended, 1, [Training Date]:[Training Date], [Date of Events]@row) +"of"+COUNTIF( registered,1) +"registered people confirmed attendance"

That would give you the value of...48 of 52 registered people confirmed attendance

I hope that helps.

Matt

fennerb

Sure! @Matt Johnson

I'm making a metrics sheet so I can put it on a dashboard for every day of class.

Matt Johnson

@fennerb

Awesome! Then my first response with the screen shot is what you'll want to set up. Everything to the left of the black line is the sheet fed from the form and to the right is the metric sheet. You got it from there.

If you have any more questions, post a screen shot of your metric sheet and the community will respond.

Thanks!

Matt

fennerb

I'm feeling kinda silly since I'm still getting "UNPARSEABLE," and I'm starting to question my own sanity. Haha.

=COUNTIFS({Attended}:{Attended}, 1, {Training Date}:{Training Date}, [Date of Events]@row)

Matt Johnson

The squiggly brackets are for cross sheet formulas so you may or may not need those. If you are doing a cross sheet forumla then you'd just have 1 "Attended" if you renamed the range to be that.

If the formula is on the same sheet as the data then remove the squiggly brackets but on the Training Date part you will need the straight brackets becuase there is a space between the words in that column header name.

=COUNTIFS(Attended:Attended, 1, [Attended Date]:[Attended Date], [Date of Events]@row)

fennerb

Thank you for your help! I was able to figure it out. :)

My brain doesn't let me understand formulas, no matter how hard I try.