# COUNTIFS 4 variables

Hi, I need help with this formula.

As you can see I am hoping to count when the name:

• Category Management is found in FUNCTION
• 100% is found in PERCENT COMPLETE
• S-CL is found in PERIOD and Day 1 is found in PERIOD

Goal is that if all attributes are found, the count and provide a total. My formula is below.

=COUNTIFS({Function}, "Category Management", {Percent}, "1", {Period}, "S-Cl", {Period}, "Day-1"

When I run this formula with just 3 attributes, which removes the Day 1 period - I gain results. So I know something is missing here, maybe a AND somewhere?

J

Tags:
«1

• I think you're missing a CONTAINS function because you're requiring that the {Period} equals both S-CL and Day-1, which isn't possible. Maybe try:

=COUNTIFS({Function}, "Category Management", {Percent}, "1", {Period}, CONTINAS("S-Cl", @cell), {Period}, CONTAINS("Day-1", @cell))

• @David Tutwiler - NAILED IT! If I might ask one more formula question around this very same topic. With respect to the % Complete. Who might I encompass the formula you provided yet have the Percent look between a range.

Example -

·      Category Management is found in FUNCTION

·      Greater than or equal to 75% and less than or equal to 99% is found in PERCENT COMPLETE

·      S-CL is found in PERIOD and Day 1 is found in PERIOD

• For that I think you're looking for something like:

=COUNTIFS({Function}, "Category Management", {Percent}, AND(@cell > 0.74, @cell < 1) , {Period}, CONTINAS("S-Cl", @cell), {Period}, CONTAINS("Day-1", @cell))

• @David Tutwiler - I updated you string in order to pull the proper columns across. Sadly - I get 'unparseable' error.

Sharing my update formula based on yours.

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.50 @cell < 74) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

I looked over this multiple times but didn't see any issues. Hoping I just missed or overlooked a small item

• Just a few format things I think. A comma and a decimal place:

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.50, @cell < 0.74) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

• @David Tutwiler - Formula worked, although it did not pull the data counts I'd expected. Sharing the VLOOKUP sheet I pull from has 5 within the criteria set out in the formula.

Any thoughts or ideas?

• Your formula says that it has to be greater than 50 and less than 74 to catch. I wouldn't be surprised if the formula returned a 0. If you want those to be included in the count you'll need to do:

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.49, @cell < 0.76) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

• @David Tutwiler - It's interesting, because when I run the initial formula, count is 0

When I run this adjusted =COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell > 0.49, @cell < 0.76) , {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

the count is 0

and then I adjusted it even further to say less than or equal to and still gained a count of 0

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell >= 0.5, @cell <= 0.74), {Finance_P}, CONTAINS("S-Cl", @cell), {Finance_P}, CONTAINS("Day-1", @cell))

This one is a bit perplexing!

• I would check all of the rest of your arguments to make sure they come back true then. Consider even taking out this entire step just to make sure you get a non-zero number. The problem could be elsewhere.

For instance, I don't see Day-1 where you have S-Cl in your screenshot. That could be causing it to return 0.

• @David Tutwiler - Great point as we both know how exact the formulas need to be!

And to your point re the Day 1 / S-CL. My goal is to aggregate a total count of all variables including if either of these are showing, not just if 1 or both are showing.

Because of that - is there a formula adjustment to consider that says - count if EITHER are present?

• Gotcha. So then you'll want to OR your last two variables:

=COUNTIFS({Finance_F}, "Risk Management", {Percent3}, AND(@cell >= 0.5, @cell <= 0.74), {Finance_P}, OR(CONTAINS("S-Cl", @cell), CONTAINS("Day-1", @cell)))

• @David Tutwiler - YOU ROCK! I can't thank you enough for sticking with me and sharing your personal time to help. I hope one day I can return the favor.

J

• No problem. Glad it's working!

• @David Tutwiler - Up for one more formula question? Using the formula you created, I am now looking for any PERCENT COMPLETE fields that are, Blank.

Does my formula look inline

=COUNTIFS({Function}, "Category Management", {Percent}, "@BLANK", {Period}, CONTAINS("S-Cl", @cell), {Period}, CONTAINS("Day-1", @cell))

• I think you need to change the "@BLANK" to ISBLANK() unless the cell literally says "@BLANK"

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!