# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  GOOGLE SHEETS: countif with dynamic range

## Headburst

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

----------


## AliGW

Welcome to the forum.  :Smilie: 

Read the instructions at the top of the page about how to attach your workbook.

----------


## Headburst

Sorry, attached the sheet now

----------


## Glenn Kennedy

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

----------


## Headburst

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

----------


## Glenn Kennedy

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?

----------


## Headburst

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.

----------


## Glenn Kennedy

I wouldn't know a Google sheet from a hole in my socks!!  Can't help you there....

----------


## AliGW

Would you like this thread moving to the Google Sheets section?

*Administrative Note:*

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!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

----------


## Headburst

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.

----------


## AliGW

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.

----------


## rorya

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,"✔")

----------

