Countif & Indirect formula

[jimmisavage]

Hi, so i'm trying to work out a formula but i'm stumped because I don't really know what it does!

Basically, I have an old worksheet which successfully uses this formula pointing to a sheet called "Caravaggio": =COUNTIFS(Caravaggio!M2:M6000,">="&EOMONTH(TODAY(),-1)+1,Caravaggio!M2:M6000,"<="&EOMONTH(TODAY(),0))

I would want to change this formula so instead of calling from Caravaggio it would call from a sheet named in cell D5 + the word " Patients" - So if Caravaggio was in D5 it would be looking in a sheet called "Caravaggio Patients".

Something like this?? =COUNTIFS(INDIRECT("'"&$D$5 & " Patients"!M2:M6000,">="&EOMONTH(TODAY(),-1)+1,"'"&$D$5 & " Patients"!M2:M6000,"<="&EOMONTH(TODAY(),0)))

Although, that doesn't work because I can't get my head around it. Is anyone able to offer an alternative suggestion? I know i'm asking a lot!

 

[Peter_SSs]

Try
=COUNTIFS(INDIRECT("'"&$D$5 & " Patients'!M2:M6000"),">="&EOMONTH(TODAY(),-1)+1,INDIRECT("'"&$D$5 & " Patients'!M2:M6000"),"<="&EOMONTH(TODAY(),0))

 

[jimmisavage]

Thank you Peter,that's done exactly what i want

 

[Peter_SSs]

Thank you Peter,that's done exactly what i want

Cheers. You're welcome.