Forum Discussion

Dawn Bardot's avatar
Dawn Bardot
Copper Contributor
Aug 31, 2017

COUNTIFS Bug?

COUNTIFS(A1:A2,"=6",A1:A2,"=1") does not return the same as COUNTIFS(A1:A2,"=6") + COUNTIFS(A1:A2,"=1")

 

A1 is 1

A2 is 6

 

COUNTIFS(A1:A2,"=6",A1:A2,"=1") returns 0

COUNTIFS(A1:A2,"=6") + COUNTIFS(A1:A2,"=1") returns 2

 

What is going on?

 

Thank you

 

 

Formulas & Functions
Need Help

5 Replies

Sort By
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Aug 31, 2017

    Dawn,

     

    it should be clear that the value of a cell can't be 1 and 6 at the same time.

    Your are probably looking for this:

    =SUM(COUNTIFS(A1:A2,{1,6}))
    • Dawn Bardot's avatar
      Dawn Bardot
      Copper Contributor
      Aug 31, 2017
      Thank you Detlef for responding. However SUM should not be needed. COUNTIFS should work and unless I have an error or there is an Excel subtly that I am not considering COUNTIFS should return 2

      COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Aug 31, 2017

        Dawn,

         

        you are not understanding how COUNTIFS() works.

         

        COUNTIFS(A1:A2,"=6",A1:A2,"=1") counts every occurance where A1=6 AND A1=1 plus where A2=6 AND A2=1.

        As I pointed out before the value of a cell can't be 6 and 1 at the same time. Thus the result for A1 is 0 and the result for A2 is 0. Added together: 0.

         

        My formula SUM(COUNTIFS(A1:A2,{1,6})) is just a shortend form of your COUNTIFS(A1:A2,6) + COUNTIFS(A1:A2,1).

         

        It is definitely not a bug.

         

Resources