Counting two things for a formula
Hi there! I'm a little stuck on a formula and could use some help...I'm looking to count the following:
Under Numbered Sites Inactive, I'd like to separate it by opted out and selected out. So I'd like to count two things from the source sheet:
- The column with the "numbered site" has a number assigned to it (so essentially, not blank)
- The column with the checkbox opted out or selected out is checked off
This is what I have so far and it just gives me zero
=COUNTIFS({Site Number}, @cell = 1, {Inactive-opted out}, 1)
The site number I put so it's counting any site that has an assigned number to it and any site that has the opted out checked off (checkbox column). Where is the logic in the formula going wrong?
Answers
-
Hi @laura.sandoval , I'm not understanding your first condition. It looks to me like it will count all the site numbers = 1. So if your site numbers are something other than 1, it won't return anything. If you are looking for non-blank site numbers, I think that NOT(ISBLANK(@cell)) should work. <>"" would also work.
Good luck and be well!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
@Scott Orsey Hi Scott! The first condition is for a column that basically assigns a site a number...so I would like the formula to only count sites that have a assigned number. I can see how that's confusing! Not necessarily summing up the numbers but only counting the site if it its not blank.
The other condition is just to count any sites that have the inactive column checked off = so counting up the total of sites that have an assigned number and have the inactive checked off.
=COUNTIFS(NOT(ISBLANK({Site Number}), {Inactive-opted out}, 1))
Is that how it would be written with the nonblank?
-
Give this a try:
=COUNTIFS({Site Number}, @cell <> "", {Inactive-opted out}, @cell = 1)
-
This worked!! Thank you @Paul Newcome !! Could you help me understand what the <> "" means? Would love to learn so I can keep this handy for the future. I feel like I've come across this a couple of times and I'm not sure how to best use it.
-
Basically it means "not equal to blank". It's the opposite of ="". "" is a null value since there is nothing between the quotes.
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
<> means not equal to
and
"" (quote quote) means blank.
You can also use this method to (for example) count all statuses that are not "Closed"
@cell <> "Closed"
and quite a few other things.
I personally prefer <> and "" as opposed to the NOT and ISBLANK functions because of how tedious the extra set(s) of parenthesis can get in longer or super complex formulas. Also fewer key strokes for us lazy folks. Haha.
-
Ohhhhh wow this is great thank you both so much!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 509 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi! I cannot figure this out. I have a "% Complete" column (0, 25, 50, 75, 90, 100, Cancelled). I made a simple checkbox column that should be checked if the item's "% Complete" value is equal to anyt…
- This formula keeps coming up as #UNPARSEABLE. I have checked the name of the columns, they are correct. Any ideas? I am trying to show the days remaining in a project. =IF([% Complete]@row = 1, "", NE…
- Hi All! I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas? =IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF(…