Cross Sheet Formula..

Lindsay Kelley
Lindsay Kelley ✭✭✭✭
edited 05/21/21 in Formulas and Functions

Hello! 

I am trying to build cross sheet reference that will tell me how many of a certain project are happening in a particular month.. However we had start and end dates we don't track anything by month. 

So my logic is =COUNTIFS({ Range 4}, "All Regions", {Range 1}, ="NAME", {Range 3}, >"Blue", {Range 5}, "BAU", {Range 6}WEEKNUMBER).. Then I can do another formula that says =IF([Value 1]52, "December".

I am sure though there is a better way to handle this though

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I follow...

    Are you able to provide a screenshot that shows some examples of what you are working with and use that to describe the count you would expect based on the data in the screenshot?

  • Lindsay Kelley
    Lindsay Kelley ✭✭✭✭

    I will try.

    So this is the live formula right now.

    I want to capture this date

    as well using =WEEKNUMBER.

    Once I have that total number. I can do an  =IF([Value 1]52, "December". so the end goal is to say

    We have 10 projects in December that are under "drive growth"... Hopefully that makes more sense

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you are wanting to count for a specific month/year, you don't necessarily need to incorporate week numbers and extra columns. You can build the range/criteria set directly in the COUNTIFS. For December of 2020, the formula would look like the below. You would just need to update the month and year number to whatever you want it to be.

    =COUNTIFS(................................................................., {Other Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2020))


    Is that the count you are looking for?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!