Count IFs or Sum IFS Formula

ConnorForm

Hello all!! So currently trying to get a sheet summary formula to show the number of Dealer ID's that are not enrolled. Pretty much just need the number of Dealer ID's shown on the SS when the Enrolled Checkbox Column is not checked. There should be 162 Dealer ID's that are not checked but the formula I am using is returning "0" or an error. I think it is because I do not have a criterion for the Dealer ID field. Any assistance is greatly appreciated!!

Been using a variation of these below!

=COUNTIFS(Enrolled:Enrolled, 0, [Dealer ID]:[Dealer ID])

=SUMIF(Enrolled:Enrolled, =0, [Dealer ID]:[Dealer ID])

ConnorForm

@Samuel Mueller this formula works for me!!!

=COUNTIFS([Dealer ID]:[Dealer ID], "<>", Enrolled:Enrolled, "")

Samuel Mueller

@ConnorForm Just add a criteria like so

=COUNTIFS(Enrolled:Enrolled, 0, [Dealer ID]:[Dealer ID], "")

Let me know if that works.

ConnorForm

@Samuel Mueller Thanks, however this formula is giving me a result of 10 when it should be 162

Samuel Mueller

@ConnorForm you are correct, I meant to send it like this this my apologies!

=COUNTIFS(Enrolled:Enrolled, 0, [Dealer ID]:[Dealer ID], "<>")

This is counting that Dealer ID is not blank, which is what I meant.

ConnorForm

@Samuel Mueller ugh!! Now it is giving me 0 as the result!! The Enrolled column is doing a vlookup to another sheet and checking the box if the other sheet has a certain value. Because this column is a cell reference, is that messing up this count if?

ConnorForm

Is is strange because if we do the formula for the checked boxes =COUNTIFS(Enrolled:Enrolled, 1, [Dealer ID]:[Dealer ID], "<>") it gives us 407 which is the correct amount for enrolled dealers but it is strange that the Non Enrolled is coming back incorrectly

ConnorForm

@Samuel Mueller this formula works for me!!!

=COUNTIFS([Dealer ID]:[Dealer ID], "<>", Enrolled:Enrolled, "")

Samuel Mueller

@ConnorForm glad you figured it out! And yes that is weird, the 0 should have worked. Wondering if the value returned from the VLOOKUP was causing the issue.