Counting two things for a formula

laura.sandoval

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?

Scott Orsey

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!

laura.sandoval

@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?

Paul Newcome

Give this a try:

=COUNTIFS({Site Number}, @cell <> "", {Inactive-opted out}, @cell = 1)

laura.sandoval

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.

Scott Orsey

@laura.sandoval

Basically it means "not equal to blank". It's the opposite of ="". "" is a null value since there is nothing between the quotes.

Paul Newcome

<> 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.

laura.sandoval

Ohhhhh wow this is great thank you both so much!!