Calculating date over large range HELP

[Tyler Smith]

Hello,

I am having trouble with getting my formula to work. I need it to count the number of dates that fall within 1/1/2018 to 1/31/2018 only if they are in the column name "Actual" This is what i have so far:

=COUNTIFS($R$3:$BA$3,"*Actual*",R4:BA4,">=1/1/2018",$R$3:$BA$3,"*Actual*",R4:BA4,"<=1/31/2018")

This works great to count the dates in that range only across one row but I need to select multiple rows to calculate the dates over a large range. Like this:

=COUNTIFS($R$3:$BA$3,"*Actual*",R4:BA25,">=1/1/2018",$R$3:$BA$3,"*Actual*",R4:BA25,"<=1/31/2018")

This way it just gives me the #VALUE error. Anyone have any insight on this?

[#2]

Hi Tyler,

That could be

=SUMPRODUCT(ISNUMBER(SEARCH("Actual",$R$3:$BA$3))*(R4:BA25>=DATEVALUE("1/1/2018")*(R4:BA25<=DATEVALUE("1/31/2018"))))

With COUNTIFS ranges shall be of the same size

[#3]

This seems to be working to find any dates within January of 2018 but it doesn't seem to be looking at only the column name "Actual".

[#4 · Accepted solution]

Sorry, it was misprint in formula

=SUMPRODUCT(ISNUMBER(SEARCH("Actual",$R$3:$BA$3))*(R4:BA25>=DATEVALUE("2018-01-01"))*(R4:BA25<=DATEVALUE("2018-01-31")))

[#5]

Thank you so much for all the help! That works great!!