Search code examples
exceldatecriteria

Excel criteria "<>"

I am looking into selecting data in excel using countif and between dates.

Using =COUNTIFS(B2:B292,">01-01-2018",B2:B292,"<31-03-2018") as an example I would like to have the "> and <" variable like for example:

A1 = 2018
A2 = 01
A3 = 03
A4 = First day in A2
A5 = Last day in A3

giving

=COUNTIFS(B2:B292,">".$A4."-".$A2."-".$A1,B2:B292,"<".$A5."-".$A3."-".$A1)

Is this possible or do anyone have a better idea.

Solution

  • Your first example should be written as 

    =COUNTIFS(B2:B292,">"&"01-01-2018",B2:B292,"<"&"31-03-2018")

    The code you have mentioned can be written as 

    =COUNTIFS(B2:B292,">"&DATE($A$1,$A$2,$A$4),B2:B292,"<"&DATE($A$1,$A$3,$A$5))

    Considering that you are always referring the A1 to A5 cells.