Forum Discussion
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") + COUN...
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.
COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
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.
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.
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)
