# Question related to year

Regular Contributor

# Question related to year

Hello Everyone,

I am just wondering how to use dcount() function or any function that could result to find the count of the year as shown below in the picture.

9 Replies

# Re: Question related to year

Considering the data in column A, you have list of years are unique ,,, so if you want to count the year, you get 1 for each.

But I don't this is the issue,, if you want to count that how many years are involved to get 36000 then you need an array formula or Solver can do this also.

So better edit your post & clear the issue,,, or share the expected output with us.

# Re: Question related to year

@Zan_Hanifee If the set-up is really as simple as you describe, and if you are willing to insert a helper column with a cumulative amount per year, XLOOKUP or MATCH could do this. See attached.

# Re: Question related to year

Thank you for giving the solution

Can you please explain this formula -

{=MATCH(TRUE,Cumulative>=12,0)}

# Re: Question related to year

From the inside out, "Cumulative>=F12" creates an array of <<FALSE, FASLE, TRUE, TRUE ....etc>> for every cell in the named range Cumulative. MATCH is finding the position of the first instance of TRUE in that array.

``{=MATCH(TRUE,Cumulative>=F12,0)}``

So, in this particular example, it finds that the third element in the array is greater than or equal to 36000. Hence, during the third year, the indicated amount has been reached or exceeded.

The curly brackets around the formula indicate that you are an older version of Excel. You can't type them when you write the formula, but you need to finish the formula by pressing Ctrl-Shift-Enter simultaneously. Then, the curly brackets will appear. On newer Excel versions this is not necessary anymore.

# Re: Question related to year

Thank you for giving the solution

Can you please explain this formula -

{=MATCH(TRUE,Cumulative>=12,0)}

# Re: Question related to year

You are awesome. Thank you so much sir

# Re: Question related to year

For pre-DA Excel

``=LOOKUP(F12,Cumulative,Years)``

shall work without CSE

# Re: Question related to year

@Sergei Baklan True, but not in case the yearly amounts are not evenly distributed.

# Re: Question related to year

@Riny_van_Eekelen , yes, it works from left to right. When to avoid CSE

``=MATCH(1,INDEX(--(Cumulative>=F12),0),0)``