Countifs Formulas

Good Morning,

I am trying to count if someone was promoted in 2020-2021 which has a Check box column or TRUE, as well as their rank. The formula is populating 0 so I must be doing something wrong? For instance we promoted 10 people to Associate Professor, 8 of which are female & 2 male.

=COUNTIFS({All Faculty List Range 3}, "true", {All Faculty List Range 4}, "Associate Professor")

Thanks,

Liz

• ✭✭✭✭✭✭

Try this:

=COUNTIFS({All Faculty List Range 3}, @cell = 1, {All Faculty List Range 4}, "Associate Professor")

• @Paul Newcome thank you so that's working but the output is wrong there should be 5 not 10 my apologies & it's populating 33?

• ✭✭✭✭✭✭

Try applying a filter to the source sheet that matches the same criteria and see how many rows come up.

• @Paul Newcome yes I did that and that's when i noticed that 5 people should be populating not 33-any ideas?

• ✭✭✭✭✭✭

Are you able to provide some screenshots? Names can be hidden so long as we can see the two ranges being evaluated?

• @Paul Newcome Please note I am working on the child rows for 20-21

• ✭✭✭✭✭✭

My apologies. I meant of the source data.

• @Paul Newcome

ahhh i see thank you for clarifying!

• ✭✭✭✭✭✭

I am assuming Range 4 is the [Current Rank] column. What is Range 3?

• @Paul Newcome my apologies for the delay!

List Range 3= Promoted in 2020-2021 & its a Checkbox

Lis Range 4= Current Rank

• ✭✭✭✭✭✭

The data in your screenshot doesn't show any checkboxes actually being checked there in the 2020-2021 column.

• @Paul Newcome the screen shot does not correct however there are 377 rows 18 or which are checked:

• ✭✭✭✭✭✭

Hmm... Did we double check the spelling already? If you have (for example) 50 spaces between two words, it will still only display as a single space, but it will not provide a match if you were searching for a single space between the two words. How is the [Current Rank] column populated?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!