Counting By Year in Multiple Tabs Not Working

[brandonjkennedy]

Hi all,

I've been struggling to try to find a simpler solution because the workaround is too cumbersome.  I'm just trying to tally every employee hired by year in multiple units, each of which have their own tab on this Excel spreadsheet.

Every google search is returning a SUMPRODUCT option, but I'm not understanding it well enough to try to adopt.  

Here is the unsightly workaround that seems to work:  

=COUNTIFS(Exec!I:I,">12/31/2021",Exec!I:I,"<1/1/2023")+COUNTIFS(Admin!I:I,">12/31/2021",Admin!I:I,"<1/1/2023")+COUNTIFS('Tech Services'!I:I, ">12/31/2021",'Tech Services'!I:I, "<1/1/2023")+COUNTIFS('Int Audit'!I:I,">12/31/2021",'Int Audit'!I:I,"<1/1/2023")+COUNTIFS('Health and Safety'!I:I,">12/31/2021",'Health and Safety'!I:I,"<1/1/2023")+COUNTIFS(Comms!I:I,">12/31/2021",Comms!I:I,"<1/1/2023")+COUNTIFS('Econ Dev Equity'!I:I,">12/31/2021",'Econ Dev Equity'!I:I,"<1/1/2023")+COUNTIFS('External Affairs'!H:H,">12/31/2021",'External Affairs'!H:H,"<1/1/2023")+COUNTIFS('General Counsel'!I:I,">12/31/2021",'General Counsel'!I:I,"<1/1/2023")+COUNTIFS(OAH!I:I,">12/31/2021",OAH!I:I,"<1/1/2023")+COUNTIFS(Licensing!I:I,">12/31/2021",Licensing!I:I,"<1/1/2023")+COUNTIFS(Enforcement!I:I,">12/31/2021",Enforcement!I:I,"<1/1/2023")+COUNTIFS(Policy!I:I,">12/31/2021",Policy!I:I,"<1/1/2023")

I really appreciate if somebody can help me modify this to a simpler format!

[#2 · Accepted solution]

@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)))))

[#3]

@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!!

[#4]

@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"))