# 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

Hello,

You can create a Named range ... such as MyRange ...

and define as follows :

Code
``=OFFSET(Sheet1!\$F\$1,8,0,COUNTA(Sheet1!\$F:\$F),1)``

and use MyRange in your formula

Code
``COUNTIF(MyRange,F9)``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Re: Sequence Formula help

Is column F included in your Table?

Can you attach a sample workbook
[sw]*[/sw]

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• 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

## Files

• Re: Sequence Formula help

Boycie - for the benefit of all following this thread, please take a moment to explain why Carim's solution is not appropriate. Thanks.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

Try the attached, I changed the formulas you are using to make all range references relative to the Table

## Files

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• 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

OK - so please extend the sample table and show us the outcomes you expect AFTER a new option has been selected.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

## Files

• Re: Sequence Formula help

How are you inserting your rows? You have deleted the formula in that cell in the attachment, which isn't much use to us ...

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

## Files

• Re: Sequence Formula help

I notice that there is no such issue if you insert a row.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• 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

=IF([Condition]<>"Exempt",COUNTIF(\$E\$9:OFFSET(\$E\$9,ROW()-9,0),E9),0)

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Sequence Formula help

Has this resolved the issue for you? Do you require any further assistance, perhaps with adapting it to your real data?

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: Sequence Formula help

Hi AliGW

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

Boycie92

• Re: Sequence Formula help

You're welcome!

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!