I tried but failed (Countif based questions)

COUNTIFS can't handle arrayed inputs, which is what's going to happen with your LEFT. So what you want is directly impossible, but there are still ways to handle it.

There are two ways I would do this:

1) Add a helper column, for example insert a new column next to CG that will be your new CH, and fill it with:
CH2 = LEFT(CG2, 2)
pull down

And then use that column CH as the condition for the COUNTIF instead of column CG.



2) Use an array-type formula instead of COUNTIFS.

For now, I'm putting this additional text in:
sheet1!A3 = "XJ"
that I will be using as a comparitor instead of the full name in sheet1!A4.

2.1) Using SUMPRODUCT()

=SUMPRODUCT(--(LEFT('MYNM Jaguar'!$CG$2:$CG$2000,2)=$A$3), --('MYNM Jaguar'!$S$2:$S$2000>=B$3), --('MYNM Jaguar'!$S$2:$S$2000<=B$4))

2.2) Using {SUM(IF(array))}

{=SUM(IF(LEFT('MYNM Jaguar'!$CG$2:$CG$2000,2)=$A$3,1,0)*IF('MYNM Jaguar'!$S$2:$S$2000>=B$3,1,0)*IF('MYNM Jaguar'!$S$2:$S$2000<=B$4,1,0))}

Note that, as a array formula, this has to be executed with CTRL+SHIFT+ENTER instead of just hitting ENTER.




See attached for a demonstration of how these would work.