CountIF incorrect

rlakin52911
rlakin52911 ✭✭✭
edited 12/09/19 in Formulas and Functions

Im trying to count the number of total number of "Open" "Risks" in another sheet

=COUNTIFS({RAID Log Range 1}, "Risk") + (COUNTIFS({RAID Log Range 2}, "Open"))

I only have 1 open risk but its returning 5. This is because its counting the total number of "open" AND "Risks" into my count. How do I write the formula to be explicit to only count Risks that are open? 

Tags:
· Share on FacebookShare on Twitter

Comments

  • rlakin52911
    rlakin52911 ✭✭✭

    Found it, this worked:

    =COUNTIFS({RAID Log Range 2}, "Open", {RAID Log Range 1}, "Risk")

    how would I add another option to Range 2

    =COUNTIFS({RAID Log Range 2}, "Open" OR "Escalated", {RAID Log Range 1}, "Risk")

    Show me all Risks that are Open or Escalated?

    · Share on FacebookShare on Twitter
  • p_bureau
    p_bureau ✭

    Hi,

     

    when you want something like "OR" with a SUMIF, you need to add multiple SUMIF :

    ' =COUNTIFS({RAID Log Range 2}, "Open", {RAID Log Range 1}, "Risk") + COUNTIFS({RAID Log Range 2}, "Escalated", {RAID Log Range 1}, "Risk") '

     

    Does this help ?

     

    Best Regards,

     

    Paul.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!