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 "Thumbs Up" 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


    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.

    :!:Forum Rules

  • 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


    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.

    :!:Forum Rules

  • 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


    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.

    :!:Forum Rules

  • 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.

    :!:Forum Rules

  • 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


    Try this instead:


    =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.

    :!:Forum Rules

  • 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.

    :!:Forum Rules

  • 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.

    :!:Forum Rules

Participate now!

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