COUNTIFS Bug?

[Dawn Bardot]

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

[#2]

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}))

[#3]

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.

[#4]

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.

[#5]

Perfect.  That helped me get it.  Thank you.  I was not understanding how the COUNTIFS function worked..  I appreciate your time replying and prompt response.

[#6]

By the way, if you compare with a number, you don't need to wrap the number in quotes.

=COUNTIFS(A1:A2,6)+COUNTIFS(A1:A2,1)