COUNTIFS 4 variables

Options

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

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

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

  • Jason H
    Jason H ✭✭✭
    Options

    @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


    Thanks in advance for your insights and formula help!

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

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

  • Jason H
    Jason H ✭✭✭
    Options

    @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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

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

  • Jason H
    Jason H ✭✭✭
    Options

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


  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

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

  • Jason H
    Jason H ✭✭✭
    Options

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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    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.

  • Jason H
    Jason H ✭✭✭
    Options

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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

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

  • Jason H
    Jason H ✭✭✭
    Options

    @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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    No problem. Glad it's working!

  • Jason H
    Jason H ✭✭✭
    Options

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

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    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!