Cross Sheet Formula..
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
-
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?
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!