Forum Discussion
Counting By Year in Multiple Tabs Not Working
brandonjkennedy VSTACK() has capability to stack data from multiple sheet to 1 sheet at once. The you can apply FILTER() function to filter desired data. Finally count filtered data. Download the attached dummy file and check it please.
=LET(dt,TOCOL(VSTACK('Exec:Int Audit'!I2:I50000),1), COUNTA(FILTER(dt,(dt>DATE(2021,12,31))*(dt<DATE(2023,1,1)))))
brandonjkennedy VSTACK() has capability to stack data from multiple sheet to 1 sheet at once. The you can apply FILTER() function to filter desired data. Finally count filtered data. Download the attached dummy file and check it please.
=LET(dt,TOCOL(VSTACK('Exec:Int Audit'!I2:I50000),1),
COUNTA(FILTER(dt,(dt>DATE(2021,12,31))*(dt<DATE(2023,1,1)))))
Harun24HR This worked, thank you! Now I need to find out why it's returning a value of one for Calendar Year 2025 since that hasn't happened yet. All of the other Calendar Years matched what I originally got with the cumbersome equation.
I appreciate your help!!
brandonjkennedy That is because of COUNTA(). Actually COUNTA() counts everything that is not empty. So, when you are calculating year 2025, filter function can't find anything and returing error. COUNTA() also count that error. So, use a slightly different approach to get accurate result. Try-
=LET(dt,TOCOL(VSTACK('Exec:Int Audit'!I2:I50000),1), IFERROR(SUM(--(FILTER(dt,(dt>DATE(2025,12,31))*(dt<DATE(2025,1,1)))<>"")),"No value found"))