excel

MID function inside COUNTIFS

I have a column of combined date and time as text format and i want to count if some data is between to dates. so i write this:

=Countifs(DATE(MID(@Data!A:A,1,4),MID(@Data!A:A,6,2),MID(@Data!A:A,9,2)),">=A7",DATE(MID(@Data!A:A,1,4),MID(@Data!A:A,6,2),MID(@Data!A:A,9,2)),"<=A8")

@Data!A:A is where i want to convert it to date:

@Data!A:A

and then check the if; ">=A7" and "<=A8" are two cells i input custom date ranges. but excel won't accept it and returns an error, says

There is a problem with this formula.

How to revise it? any thoughts?

Error message

enter image description here

I tried as above and I don't want to make extra columns for separate calculations.

Solution

  • The problem is that the COUNTIF* functions do not accept the dynamic arrays.

    Also @ return a single value while you need to have an array to count something within.

    This formula can be a solution for you:

    =SUM(
  (DATE(MID(Data!A1:A10,1,4),MID(Data!A1:A10,6,2),MID(Data!A1:A10,9,2))>=A7)*
  (DATE(MID(Data!A1:A10,1,4),MID(Data!A1:A10,6,2),MID(Data!A1:A10,9,2))<=A8)
)

    And A:A is not a good range to use it here. Narrow it for the data area only:

    =LET(
  _r,INDIRECT("Data!A1:A"&MATCH(2,1/(Data!A:A<>""))),
  SUM(
    (DATE(MID(_r,1,4),MID(_r,6,2),MID(_r,9,2))>=A7)*
    (DATE(MID(_r,1,4),MID(_r,6,2),MID(_r,9,2))<=A8)
  )
)