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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityMy 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=""))
.
=COUNT(IF(G2:G16=H2:H16,1,""))-SUM((G2:G16="")*(H2:H16=""))
Jul 28, 2017 at 16:45