# CountIf Comparing Dates

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.  What's going on with this?

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.

• 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