Forum Discussion
Calculating date over large range HELP
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?
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")))
4 Replies
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
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".
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")))
