Concatenation on CountIf Formula

[IonutC]

Guys,

Need some help with a topic.

My given formula is

=COUNTIF('FCST WRONG STAGE'!M:M,B2)

Now in C1 i have the name of the sheet FCST WRONG STAGE.
How can i make the formula to add the sheet name from C1?

I need this, just because when I delete the sheet from source, the formulas retrieve a #REF! error and this sucks!

I need this formula to paste it in the cell and to retrieve automatically the data, when the sheets are automatically uploaded to the source.

tried until now "=COUNTIF("'"&C1&"'!M:M,B2)"
Thank you!

[romperstomper]

You need INDIRECT:

=COUNTIF(INDIRECT("'"&C1&"'!M:M"),B2)

[IonutC]

rorya you rock! super thank you!

[romperstomper]

Glad to help - thanks for the rep.

[IonutC]

another one pls =SUMIF('PAST CLOSE DATE'!$A:$R,B2,'PAST CLOSE DATE'!$M:$M) how is this one becoming? tried =SUMIF(INDIRECT("'"&$E$1&"'!$A:$R"),B2,INDIRECT("'"&$E$1&"'!$M:$M)) but it didnt work

[romperstomper]

You missed the last quotes:

=SUMIF(INDIRECT("'"&$E$1&"'!$A:$R"),B2,INDIRECT("'"&$E$1&"'!$M:$M"))