1

My CountIf function is straight just not working, and I'm curious what I've done wrong. It only seems to output 1 or 0, and bizarrely is doing so differently depending on where in the sheet I put the data.

enter image description here

enter image description here

What's going on with this?

0

1 Answer 1

2

COUNTIF can compare a range to a value, and it returns a scalar. It can compare a range to a range, but in that case it returns an array instead, so you should sum up those values with SUMPRODUCT:

=SUMPRODUCT(COUNTIF(G2:G16,H2:H16))
=SUMPRODUCT(COUNTIF(C10:C24,D10:D24))

You could also use a CSE formula (CSE means you have to press CTRL+SHIFT+ENTER instead of just ENTER):

=COUNT(IF(G2:G16=H2:H16,1,""))
=COUNT(IF(C10:C24=D10:D24,1,""))

This also counts blank cells when compared to other blank cells, so it might not be what you want.

Update: I'd like to include Bandersnatch's solution that separately counts the blanks and subtracts them from the total: =COUNT(IF(G2:G16=H2:H16,1,""))-SUM((G2:G16="")*(H2:H16="")).

See a similar problem solved here and here.

1
  • 1
    Great answer. Using SUMPRODUCT() is the simple and elegant solution. But if for some reason you need to use the array formula, this modification will subtract out the blanks. =COUNT(IF(G2:G16=H2:H16,1,""))-SUM((G2:G16="")*(H2:H16="")) Jul 28, 2017 at 16:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .