Forum Discussion

Tyrell Williams's avatar
Tyrell Williams
Copper Contributor
Nov 17, 2017

Refering to a date within another cell when using the COUNTIFS formula

Hello Im trying to use countifs and one of the criteria is a date. I just need the month really. The dates are formatted as MM/DD/YYYY but I cant seem to write the proper function. I tried countifs(...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 17, 2017

Could be

=SUMPRODUCT((MONTH(range)=2)*(YEAR(range)=2017))
  • Tyrell Williams's avatar
    Tyrell Williams
    Copper Contributor
    Nov 17, 2017

    Will this work? What im doing is counting the number of entries by specific person in a specific place in a specific month. Its just that the dates are all MM/DD/YYYY. and for some reason I cant us "2*" or "2/??/2017" as my criteria for the range in which the dates are held.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 17, 2017

      Hi Tyrell,

       

      To SUMPRODUCT you may add as many criteria as you need, if they are with AND condition.

       

      In COUNTIFS you try to compare each date in the range (which is number) with some text, that never returns TRUE. You may add couple of conditions into COUNTIFS

      =COUNTIFS(range,">=" & DATEVALUE("2017-02-01"),range,"<="&EOMONTH(DATEVALUE("2017-02-01"),0))

      or like, where you compare dates with dates (aka numbers with numbers)

       

      • Tyrell Williams's avatar
        Tyrell Williams
        Copper Contributor
        Nov 17, 2017

        Sorry, I'm a bit confused, my current formula is =COUNTIFS(range,"name", range2,"place", range3 ,"2/22/2017")

        i get a count when I put and exact date for the criteria, but i need a way to do it month by month

         

        should I just include the statements you wrote previously with the same range for the two criteria? 

Resources