Stack CountIFS within one cell

HI,

I'm trying to produce a table that contains projects received within a year combined, then separated into regions. Below is the formula I'm using, I can only guess that it's subtracting the 345 from the 2106 instead of only counting the USA projects within the 345.


=COUNTIF({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31)) - COUNTIFS({State}, <>"CAN-Canada", {State}, <>"OC-Outside North America")



Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    edited 10/04/23 Answer ✓

    Hi @TravisClem ,

    I'm not sure what you are using for the formula in the "ALL" column, but if it's the same as the first section of your posted formula, I would think you are getting the wrong results as it would count every year up to the year in question (as opposed to only the year in question).

    Either way, try this for the USA column.

    =COUNTIFS({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31), {Projects In by Date}, >=DATE([Primary Column]@row, 1, 1), {State}, <>"CAN-Canada", {State}, <>"OC-Outside North America")

    The formula I have posted ensures only 1 year is looked at - use the first portion if it does turn out your other formula is incorrect.

    Hope this helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    edited 10/04/23 Answer ✓

    Hi @TravisClem ,

    I'm not sure what you are using for the formula in the "ALL" column, but if it's the same as the first section of your posted formula, I would think you are getting the wrong results as it would count every year up to the year in question (as opposed to only the year in question).

    Either way, try this for the USA column.

    =COUNTIFS({Projects In by Date}, <=DATE([Primary Column]@row, 12, 31), {Projects In by Date}, >=DATE([Primary Column]@row, 1, 1), {State}, <>"CAN-Canada", {State}, <>"OC-Outside North America")

    The formula I have posted ensures only 1 year is looked at - use the first portion if it does turn out your other formula is incorrect.

    Hope this helps,

    Dave

  • That solved the issue :) Thanks @DKazatsky2 , I thought I needed to start a new COUNTIF in order to then sort by region.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!