# GOOGLE SHEETS: countif with dynamic range

1. ## GOOGLE SHEETS: countif with dynamic range

I have created a basic attendance sheet for our football team for the new year. We normally run training sessions on a Wednesday and a Sunday. If a player attends, they get a green check, if they don't it's a red cross. So that's pretty simple to count up with ''countif''

What i'm trying to do is create a formula that will count the last 10 sessions we have run. "=COUNTIF(A1:A10,"✔️")" The problem with that formula is that not only would the range have to update every session "A2:A11" but there are some Wednesday's and Sundays when we don't run a session and i need this to be skipped.

I thought about created a referance "attendee'' that would have perfect attandance and the range could be worked from that. So it would count 10 "✔️" maybe output the range to another cell and the other formula picked up from there.

A bit stuck. Any ideas would be much appreciated

2. ## Re: countif with dynamic range

Welcome to the forum.

3. ## Re: countif with dynamic range

Sorry, attached the sheet now

4. ## Re: countif with dynamic range

Unfortunately, this doesn't help hugely. it's blank. Some manually calculated expected results where you want to see them would help!!!

5. ## Re: countif with dynamic range

Hi, sorry about that. I have cut it down and added in some data. Hopefully that will help.

6. ## Re: countif with dynamic range

Yep. It does!!

=COUNTIF(INDEX(17:17,AGGREGATE(14,6,((\$C\$21:\$BK\$21="✔")*(COLUMN(\$C\$21:\$BK\$21))),10)):BK17,"✔")

copied down. How does that look?

7. ## Re: countif with dynamic range

Yes! That works great thanks for that.

I thought it would port over to Google Sheets but for some reason the last range '':BK17,'' is not picked up in there.

9. ## Re: countif with dynamic range

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

10. ## Re: countif with dynamic range

Yeah please! Glenn's formula worked a treat. Can't thank him enough.

Google sheets returns the value as zero. It doesn't look like it picks up the last cell range '':BK17''. Removing the ":", it seems to pick it up but gives a parse error.

11. ## Re: countif with dynamic range

You have just quoted an entire post in which I asked you NOT to quote entire posts! Please stop doing this.

I shall move the thread for you.

12. ## Re: GOOGLE SHEETS: countif with dynamic range

Sheets doesn't have an AGGREGATE function, so use something like:

=COUNTIF(INDEX(17:17,LARGE(((\$C\$21:\$BK\$21="✔")*(COLUMN(\$C\$21:\$BK\$21))),10)):BK17,"✔")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1