How to change value in google sheet function

Question

I was trying to countif the order in the sheet. the sheet name indicates the day in month January. column A indicates the date (D MMM) the order taking place. therefore I use the function below for 1 Jan

=countifs('1'!A:A,B2)

I wanted to know if its possible to change the function to

=countifs('X'!A:A,B2)

X being the date of the order in Column A.

Link: https://docs.google.com/spreadsheets/d/1fS0WUEm-CD3YljH55SgjjTq0JQG0FerEOoY2YaVbTUA/edit?usp=sharing

Answer 1

Alternative method to extract the numbers from A2:

=COUNTIF(INDIRECT(REGEXEXTRACT(text(A2,"d mmm"),"\d")&"!A:A"),B2)

If you were able to consolidate all of your tabs onto one tab, then you could have an ARRAYFORMULA that would get all of your COUNTIF without needing to drag down the formula.

A consolidated tab would therefore need 'Order date' and 'Order number' columns.

Answer 2

Use INDIRECT() function.

=Countifs(INDIRECT(LEFT(A2,SEARCH(" ",A2)-1)&"!A:A"),B2)