Count IFs or Sum IFS Formula
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])
Best Answer
-
@Samuel Mueller this formula works for me!!!
=COUNTIFS([Dealer ID]:[Dealer ID], "<>", Enrolled:Enrolled, "")
Answers
-
@ConnorForm Just add a criteria like so
=COUNTIFS(Enrolled:Enrolled, 0, [Dealer ID]:[Dealer ID], "")
Let me know if that works.
-
@Samuel Mueller Thanks, however this formula is giving me a result of 10 when it should be 162
-
@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.
-
@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?
-
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
-
@Samuel Mueller this formula works for me!!!
=COUNTIFS([Dealer ID]:[Dealer ID], "<>", Enrolled:Enrolled, "")
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!