CODEWITHSUNDEEP
google-sheetscountgoogle-sheets-formula
taylourasaur
taylourasaur

Reputation: 1

Count amount of cells within a specific year

What I want is for a single column (C-L respectively) to count exactly how many cells in their respective row match the same year (from P onward) as the labeled column. So L5(red) will count how many cells from P5-Z5 have "2022" and K8(pink) will count how many cells from P8-Z8 have "2021" in them.

I currently have the number manually entered into each cell, but would like to automate it so it will count the years on its own. Every string of formulas I have tried all come up as error. It was easier to get a second page to count the amount of cells that have a specific word. But now I can't get a single page to count how many cells contain the specific year date in it on its own page.

Here are various logic formulas I've tried. Each of them just comes up as error.

=COUNTIFS(!P5:cc5,(2022))
=COUNTIFS(!p5:cc5,"&2022")
=COUNTIFS(!p5:cc5,\<="2022")
=COUNTIFS(!p5:cc5,"\<=01/01/2022")
=COUNTIFS(!p5:cc5,"\>=01/01/2022",!p5:cc5,"\<01/01/2023")
=COUNTIF(!P5:cc,YEAR(2022))
=COUNTIFS(!P5:cc,"\<="&DATE(2022))
=(COUNTIF(!p5:cc,"\>="01/01/2022)-COUNTIF(!$p$5:$cc,"\>="01/01/2022))

This one is the formula I have for reading the second page to count how many times the specific name shows up. O5 is the cell with the name in it. So I was basing my year counting off this and trying to google my way through it.

=(COUNTIF('Queue List'!$B$3:$D$400,O5))

Sheet layout

Upvotes: 0

Views: 508

Answers (1)

Mart&#237;n
Mart&#237;n

Reputation: 10217

As far as I understand you have 10 columns (C-L) that will have to find how many dates in P:CC are in year 2013,2014,2015... right? You can do it like with this formula in C5:

=COUNTIF(ARRAYFORMULA(YEAR($P5:$CC5)),2010+COLUMN())

You're "scanning" the year of the whole row with arrayformula, and then seeing if it matches 2022. In this case I changed 2022 with 2012+COLUMN() so you can drag it or copy and paste to the whole column and the number of column added to 2012 will "calculate" the corresponding year of each column

Another option is to create a whole array with one single formula in C5:

=MAKEARRAY(ROWS(P5:P),10,LAMBDA(r,c,IF(COUNTA(INDEX(P5:CC,r))=0,"",COUNTIF(ARRAYFORMULA(YEAR(INDEX(P5:CC,r))),2012+c))))

Obviously you can adapt it to a specific range. Right now it creates a "rectangle" of 10 columns wide (C-L) and to the bottom of the page (counting the rows between P5 to P (the end of the sheet). "r" and "c" are the number of the row and the column of each cell being calculated (C5 is Row 1, Column 1. D7 is Row 2, Column 3, etc). With INDEX you can select the row to count from the whole range (using that "r" that equals the row), and with c I use the same logic that with the other formula in order to add to 2012+1 in C, 2012+2 in D, 2012+3 in E, etc.

And COUNTA checks first if there is any value in that row, if it doesn't it leaves that row empty (so you won't have a bunch of unnecesary "0"

Upvotes: 0

Related Questions