# Cross Sheet Formula..

Options
✭✭✭✭
edited 05/21/21

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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!