Cross Sheet Formula..

Lindsay Kelley

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

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

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

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?