Forum Discussion

Kylee patrick's avatar
Kylee patrick
Copper Contributor
Feb 25, 2017

Error Message Countif

In Excel 2013: Im trying to count the names in column A (not including TBA) =COUNTIF A2:A40, "<TBA")

plus the number of Fitters in column B =COUNTIF A2:A40, "<TBA", B2:B40,"=Fitter",

plus year in column E, (only if greater than 2017) however I keep getting an error message?? Please help

 

=COUNTIF A2:A40, "<TBA", B2:B40,"=Fitter", E2:E40, ">2017")

4 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Dear,

     

    Try this formula:

    =SUM(COUNTIF(A2:C40,{">12/31/2017","<>TBA","Fitter"}))
    
    But you have to move the date column from column E to C, and select all the three columns 
    
    If you can't do that try this:
    =SUM(COUNTIF(A2:B40,{"<>TBA","Fitter"}))+COUNTIF(E2:E40,">12/31/2017")

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor

      Hi Haytham,

       

      Array with COUNTIF generates OR condition, and here we need AND condition. I'd use

      =SUMPRODUCT(--(A2:A40<>"TBA"),--(B2:B40="Fitter"),--(E2:E40>=43101))

      where 43101 = Jan 01, 2018 (to avoid specific of regional settings)

       

       

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor

        Hi Sergei,

         

        I have understood from her question that she want to count each criterion seperately using OR logic.

        Anyway, she can choose the solution that agree what she meant.

         

        By the way, thanks about your point to use the serial number of date in stead of the date format to avoid some different regional settings.

Resources