How to add a Row Number to a formula

[Nickmsi]

When a “2” appears in Column AO, I need the formulas in rows C to AM to change:
Example:

The first “2” appears in row cell AO14.

Formula in cell C14 is Countif($A$5:A14,C$5)

In cell AW15 we need the formula to be (Countif($A$15:A15,C$5)

We need to change $A$5 to the current row which would be $A$15.

Thanks for all your help

[mrice]

I don't fully understand the question but maybe this will help.

=COUNTIF(INDIRECT("$A$5:$A14"),C$5)

is equivalent to

=COUNTIF($A$5:$A14,C$5)

To find the row number of first occurrence of 2 in column AO, you can use

=MATCH(2,AO:AO,0)

You should be able to build your COUNTIF range by using formulae to create the string used in the INDIRECT function.

[Nickmsi]

Thank you for your reply.

What I need is to have the $A$5 part of the Countif($A$5:$A14,C$5) change each time a 2 appears.

So if a 2 appears in row 14, the row 15 would be (Countif($A$15:$A15,C$5). I need a new cycle to start at the row number.

If a 2 then appears again in row 22 it would change on row 23 to (Countif($A$23:$A23,C$5)

So everytime a 2 appears, we need to change the beginning of the Countif ($A$23) which is the row number.

I hope this makes more sense and as always appreciate your help

[mrice]

Hmmmm....

Will you not get a circular reference?

column AO depends on C and C depends on AO if I have understood this correctly.

[FlameRetired]

I am a little confused.

Is there any chance this is a typo?

In cell AW15 we need the formula to be (Countif($A$15:A15,C$5)

[Nickmsi]

mrice, I don't think there will be a circular reference, for example a "2" appears in CELL AO14, (Row 14) we then change the formula in the next row(Row 15), or specifically in cells C15 through AM15 we will change from Countif($A$5:$A14,C$5) to Countif($A$15:$A15,C$5). Cell AO15 will then be "1" and we start looking for a "2" again.

How do I change the formula Countif($A$5..... to Countif($A$&"Row Number"...??

Yes FlameRetired, it was a typo, it should have read C15 and not AW15.

Any help always appreciated.

[Nickmsi]

I may not have been too clear in my explanation:

When we get a 2 in Cell AO14, Yellow Column AO, I need to change the formula in Cell C15 through am15 as follows:

If(ao14=2,countif($A$(row15):$A15,C$5),countif($A$5:$A15,c$5)) or
If(ao14=2,countif($A$(B15):$A15,C$5,countify($A$5:$A15,C$5))

The end result would be: if(ao14=2,countif($A$15:$A15,C$5),countif($A$5:$A15,C$5))

Every time we get a 2 in Column AO we need to resent the starting counter in the Countif to the next row.

Is it possible to do this?

Many thanks

[Pepe Le Mokko]

Your post does not comply with Rule 3 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).

Please make sure to include the full link, so that other members will be permitted to continue helping you

[Nickmsi]

I solved the problem by using a different way to count, here is what I did

IF(AND($CI150=2,$A151=AW$4),1,IF(AND($CI150=2,$A151<>AW$4),0,IF($A151=AW$4,AW150+1,AW150)))

Thanks for all your help.
Cheers

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

Sorry, but your post does not comply with Rule #6 of our Forum RULES.: please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction, then send a private message to them asking for clarification.

Thread closed