Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Nested IF isn't working

    ✭✭✭
    in Formulas and Functions

    Hi, I'm trying show offers declined for a certain region by month with the following formula:

    =IF(COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Product Management", {TA_Offer Decline data Range 1}, "Engineering", {TA_Offer Decline data Range 1}, "IT", {TA_Offer Decline data Range 1}, "Security", {TA_Offer Decline data Range 1}, "Cloud Ops", {TA_Offer Decline data Range 2}, $May$2) = 0, "", (COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Product Management", {TA_Offer Decline data Range 1}, "Engineering", {TA_Offer Decline data Range 1}, "IT", {TA_Offer Decline data Range 1}, "Security", {TA_Offer Decline data Range 1}, "Cloud Ops", {TA_Offer Decline data Range 2}, $May$2)))

    I'm guessing it's an issue w/ my nested IF(COUNTIF but hitting a wall...here's what the sheet looks like. The zeros being returned are incorrect.

    image.png


    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭

      Hey Alex -

      Correct me if I'm wrong - but it looks like you want to look at offers declined PER Type or department?

      If that's the case - it seems your COUNTIFS isn't working, because you actually want to do multiple COUNTIFS and sum them - (I only did the 1st half of what I think you'll need)

      Where each COUNTIFS checks to reference sheet for the Region match, the individual department match & the date reference match, then sums together.


      =IF(SUM(COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Product Management", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Engineering", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "IT", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Security", {TA_Offer Decline data Range 2}, $May$2), COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, "Cloud Ops", {TA_Offer Decline data Range 2}, $May$2)) = 0, "", **Insert second half here**


      Let me know if that helps or not!

      I'd love to take another crack at it if that doesn't solve your issue.


      -Jon

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      The reason you are getting zeroes is coz you are trying to match all of those conditions in a single range {TA_Offer Decline data Range 1} and if will always return false/0 as you cannot have all values in the same cell. Assuming you are looking for the count of all those cells in the column where one of those values exists you can try the below,

      =IF(
COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, OR(@cell ="Product Management", @cell ="Engineering", @cell = "IT", @cell = "Security", @cell = "Cloud Ops"), {TA_Offer Decline data Range 2}, $May$2) = 0,
"",
COUNTIFS({TA_Offer Decline data Range 3}, $Region@row, {TA_Offer Decline data Range 1}, OR(@cell ="Product Management", @cell ="Engineering", @cell = "IT", @cell = "Security", @cell = "Cloud Ops"), {TA_Offer Decline data Range 2}, $May$2)
)


      · Share on FacebookShare on Twitter
    • ✭✭✭

      Thank you both for your comments. Sameer's formula above gave me what i need.

      Cheers,

      Alex

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions