Closed Thread
Results 1 to 26 of 26

Counting Multiple Sequences Within One Formula

  1. #1
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Counting Multiple Sequences Within One Formula

    hi ,i have attached a workbook with 2 examples,i have a formula at D1 which counts a certain event at C:C and at H1 which counts the numbers 1,2 at G:G.what i need is to make one formula which states that : if the last 2 nubers and the sixth and seventh and same with the eleventh and twelveth number(from below) are the same value and order and the 3,4,5 with the 8,9,10 numbers are also the same value and order count 12.i need only one formula to count everything .thanks
    Last edited by loucifer777; 05-23-2020 at 10:37 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,349

    Re: if and formula

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Counting Multiple Sequences Within One Formula

    Maybe try

    =SUMPRODUCT(--(C6:C98<>C7:C99))

  4. #4
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    hello bo_Ry! i put the formula at D1 and it gives me a number 5.i think i didnot explained as it could be.i need a formula at D1track the numbers 1 and2 which are random at C:C ,which states that if the last two numbers from below and the sixth and seventh and eleventh and twelveth numbers are the same value and order(that is the first statement), the second is that the numbers in betwwen (which are situated in 3,4,5 with the 8,9,10 cell from below)are also the same,,count 12

    please see attached more examples,we need one formula to count all sequences with that order
    Last edited by loucifer777; 05-23-2020 at 11:22 AM.

  5. #5
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    how do we say if the lat number in a row is equal with the previous number say 'yes' for example?

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting Multiple Sequences Within One Formula

    Hi,

    Based on the workbook from post #4:

    =SUM(N(MMULT(N(N(OFFSET(C6,{5,6,6,10,11,11,7,8,9},{0;4;7;10}))=N(OFFSET(C6,{0,1,1,0,1,1,2,3,4},{0;4;7;10}))),{1;1;1;1;1;1;1;1;1})=9))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    dear XOR LX thank you ,can you put the formula at workbook,cause i could not put it right

  8. #8
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    and also please consider that the values at C:C might go to C100 ,formula should look from c1:c100 or c:c

  9. #9
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    do you need more info?

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Counting Multiple Sequences Within One Formula

    I have no idea what you try to count.
    This is just guessing.

    G1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Ctrl+Shift+Enter
    Attached Files Attached Files

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting Multiple Sequences Within One Formula

    Array formula**:

    =SUM(N(MMULT(TRANSPOSE(ROW(C6:C100)^0),N(N(OFFSET(C6,ROW(C6:C100)-MIN(ROW(C6:C100)),{0,4,7,10}))=N(OFFSET(C6,MOD(ROW(C6:C100)-MIN(ROW(C6:C100)),5),{0,4,7,10}))))=ROWS(C6:C100)))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  12. #12
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    dear XOR LX ,thank u so much,where i copy/paste the formula?
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    can u help me on the attached workbook above?

  14. #14
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    dear bo,thank you,i try but i cant..

  15. #15
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    it seams something is not clear in the workbook,it shows that sign:#N/A
    Attached Files Attached Files
    Last edited by loucifer777; 05-23-2020 at 03:47 PM.

  16. #16
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting Multiple Sequences Within One Formula

    Quote Originally Posted by loucifer777 View Post
    dear XOR LX ,thank u so much,where i copy/paste the formula?
    Anywhere you want.

    On second thoughts, if there are not necessarily entries all the way to row 100 then use:

    =SUM(N(MMULT(TRANSPOSE(ROW(C6:C100)^0),N(N(OFFSET(C6,ROW(C6:C100)-MIN(ROW(C6:C100)),{0,4,7,10}))=N(OFFSET(C6,MOD(ROW(C6:C100)-MIN(ROW(C6:C100)),5),{0,4,7,10}))))=COUNT(C6:C100)))

    Returns 4 for the workbook provided.

    Remember to commit with CTRL+SHIFT+ENTER!

    Regards

  17. #17
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    dear bo,i dont want to count,just when the sequence apears9any of the sequences),i want the formula to write 12 or ''yes''

  18. #18
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    dear xor lx,thank you for your time which you spend to me ,really thank you.i trey to copy paste but i get an error message can you help me?
    Attached Files Attached Files

  19. #19
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Counting Multiple Sequences Within One Formula

    Quote Originally Posted by loucifer777 View Post
    dear bo,i dont want to count,just when the sequence apears9any of the sequences),i want the formula to write 12 or ''yes''
    Ok, I'm lost now. I supplied a formula based on your earlier statement:

    Quote Originally Posted by loucifer777 View Post
    we need one formula to count all sequences with that order
    Best of luck.

  20. #20
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    dear all ,i just mean that : we have a column which i put manually randomly the numbers 1 and 2,yes? the needed formula should track the column and when the sequence happened should write ''yes'' or ''good".now what is the sequence? example 1: 12 111 12 111 12 ,example 2: 22 221 22 221 22, example 3: 12 112 12 112 12, example 4: 21 112 21 112 21, example 5: 11 222 11 222 11.....so if you see this outcome in a the column c:c for example what we see? we see that the last 2 numbers with the 6,7,11,12 are equal in value and in order.the same with the numbers between.

    formula should say :if in c:c the last 2 numbers are equal with the last sixth and seventh and equal with eleventh and twelveth and the same time the last 3,4,5 numbers are equal and same order with 8,9,10 say yes ,if no say zero

    so we track column and when this happend say"yes"otherwise zero.thanks again!!
    Last edited by loucifer777; 05-24-2020 at 01:06 AM.

  21. #21
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    i really apriciate your try

  22. #22
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Counting Multiple Sequences Within One Formula

    @loucifer777

    for your question below:
    https://www.excelforum.com/excel-gen...-sequence.html

    B1 =IF(COUNTIF($A$1:$A$10,$A1)=COUNTIF($A$1:$A1,$A1),"WOW","") and drag below.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  23. #23
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    not exactly,cause the results at A:A might g0 to 300 or 1000, i ask something which should not be so difficult to understand.please give one formula correct please in this forum

  24. #24
    Registered User
    Join Date
    03-29-2020
    Location
    cyprus
    MS-Off Ver
    2010
    Posts
    50

    Re: Counting Multiple Sequences Within One Formula

    i said the lat with fifth and seventh,yes??
    and at my previous post nobody gave a correct formula for something not so hard for the experts here..so is better before any your ansewer to tell us how much money you want and dont take our time

  25. #25
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Counting Multiple Sequences Within One Formula

    not exactly,cause the results at A:A might g0 to 300 or 1000, i ask something which should not be so difficult to understand.please give one formula correct please in this forum
    Then amend the range

    B1 =IF(COUNTIF($A$1:$A$10000,$A1)=COUNTIF($A$1:$A1,$A1),"WOW","") and drag below.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,349

    Re: Counting Multiple Sequences Within One Formula

    Quote Originally Posted by loucifer777 View Post
    not exactly,cause the results at A:A might g0 to 300 or 1000, i ask something which should not be so difficult to understand.please give one formula correct please in this forum
    Quote Originally Posted by loucifer777 View Post
    i said the lat with fifth and seventh,yes??
    and at my previous post nobody gave a correct formula for something not so hard for the experts here..so is better before any your ansewer to tell us how much money you want and dont take our time
    With that ungrateful attitude, you can take your queries elsewhere.
    Last edited by AliGW; 05-24-2020 at 09:06 AM.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 03-24-2020, 07:27 AM
  2. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  3. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  4. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  5. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  6. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  7. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1