Stop countif with circular reference

[Tero10]

Hi,

My problem is easier to understand with the excel open. What I want is to enter a certain number of stores to be opened in a given year and once that number has been reached to pass to the openings of the second year, that until the number of stores of the last year have been reached and then the result is blank. Unfortunately, since the the result is dependent of the COUNTIF() which itself is dependent of the result the formulas gives a circular reference. Is there any way to give the number of stores of the first year and then once that number has been reached to pass to the second part of the formula?

Thank you in advance for your help

[AliGW]

What exactly is the purpose of the vertical range?

[Tero10]

Thx for the quick reply

The vertical range is the number of stores so normally I should have 2 "2018", 1 "2019", 3 "2020", 2 "2021", 1 "2022"

[AliGW]

I don't understand. Every cell in that vertical array has the same formula. What do you mean when you say that the range is the number of stores? Can you please let us know what your expected outcomes are and where they should appear?

[Tero10]

Yes what I am trying to do with the formula is that once the number of stores has reached the number in row 8 the result should be the following year and once that number has been reached the one after that and on and on. The expected outcome should be:

2018
2018
2019
2020
2020
2020
2021
2021
2022
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK

Thx for your patience

[AliGW]

OK. In H15, type this:

=H7

In H16 this, copied down:

=IFERROR(IF(COUNTIF($H$15:H15,INDEX($H$7:$L$7,,MATCH(H15,$H$7:$L$7,0))) < INDEX($H$8:$L$8,,MATCH(H15,$H$7:$L$7,0)),H15,H15+1),"")

You do not need the data in the range H10 to L11.

Note the double comma is NOT a typo!

[AliGW]

A sligtly better version:

=IFERROR(HLOOKUP((IF(COUNTIF($B$15:B15,INDEX($B$7:$F$7,,MATCH(B15,$B$7:$F$7,0))) < INDEX($B$8:$F$8,,MATCH(B15,$B$7:$F$7,0)),B15,B15+1)),$B$7:$F$7,1,0),"")

[Tero10]

Amazing it works! Thank you very much!

[AliGW]

You're welcome!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED and consider clicking on the reputation button to thank anyone who helped you . Thanks.

[Tero10]

One last question, if I put 0 in one of the cells row 8 I still have one entry of that year. Would it be possible to give the following year as a result instead?

Thx

[AliGW]

Working on it - nearly there!

[AliGW]

Sorry - I can't make it work for the situation where there might be more than one consecutive zero.

[Pepe Le Mokko]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).