Sequence Formula help

Hi,

I was wondering if someone could help.

I have this formula:

=IF([@[Stage*]]<>"Exempt",COUNTIF($F$9:F9,F9),0)

It has been used to count the number of instances per term used in column F.

I have a table the problem is that when i add in new rows the formula in the row above alters and says something like this

=IF([@[Stage*]]<>"Exempt",COUNTIF($F$9:F11,F10),0)

Is there any way to stop this from happening?

Thanks,
Boycie92

Re: Sequence Formula help

Hi,

Carim, i have tried your suggestion and it is not exactly what i need. If you look at my example, you will understand why.

KjBox - Yes column F is within the same table. Please see my attached example.

Thank you both,
Boycie92

Re: Sequence Formula help

My apologies,

This assumes i have used the solution correctly. The named range only applies to one option (cell F9) . So if i have five terms in column F, the first one (F9) would be used.

The count if part would then only count the number of instances for the term in cell F9.

Again this is based on the way i have used the solution, if i have applied it wrong, then please let me know.

Thanks,
boycie92

Re: Sequence Formula help

Thank you for taking the time to help me with this.

I don't think i have been very clear about my problem so i apologies for that.

Essentially i need the formula to count each instance of a term,

So if A appears once (Cell F9) then in cell G9 it should say 1
If B appears once (Cell F10) then in cell G10 it should say 1
If C appears in Cells F11 & F12. In Cells G11 it should say 1 and in cell G12 it should say 2.

The sequence should restart once a new option has been selected.

I hope this helps make it a bit clearer! Sorry Again,

Boycie92

Re: Sequence Formula help

Apologies for the massive delay. Please see that attached example.

I have added in a new row and you can see in G13 that the formula has changed to this: =IF([Condition]<>"Exempt",COUNTIF($E$9:E14,E13),0) when it should be this =IF([Condition]<>"Exempt",COUNTIF($E$9:E13,E13),0)

It seem to start referencing the new row that's been added

Thanks,

Boycie92

Re: Sequence Formula help

Hi,

apologies, I didn't realize I deleted it!

I have attached the correct example now.

when I add in a new row I am just typing in bellow the previously row so I will start writing a date into B14 this creates a new record in the table. As soon as I create it, the formula in G13 changes.

Thanks,
Boycie92

Re: Sequence Formula help

Strange. I have the issue with the old last row and not the new one. Please see below:

G9 =IF([Condition]<>"Exempt",COUNTIF($E$9:E9,E9),0)
G10 =IF([Condition]<>"Exempt",COUNTIF($E$9:E10,E10),0)
G11=IF([Condition]<>"Exempt",COUNTIF($E$9:E11,E11),0)
G12 =IF([Condition]<>"Exempt",COUNTIF($E$9:E12,E12),0)
G13 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E13),0)
G14 =IF([Condition]<>"Exempt",COUNTIF($E$9:E14,E14),0)

if it was to insert another row (15) it would look like this:

G14 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E14),0)
G15 =IF([Condition]<>"Exempt",COUNTIF($E$9:E15,E15),0)

See how whenever I enter a new row the formula in the previous last row changes? this is the issue I am having. I don't wait it to change.

Thanks,
Boycie92

Re: Sequence Formula help

Hi AliGW

That's seems to have done the trick. Thank You!

Boycie92