+ Reply to Thread
Results 1 to 4 of 4

Count if previous element doesn´t have specific value

  1. #1
    Registered User
    Join Date
    07-19-2018
    Location
    Germany
    MS-Off Ver
    Office 2016
    Posts
    2

    Count if previous element doesn´t have specific value

    Hey guys,

    I´m a newbie to excel and have a big problem. I have large tables that include different strings. For me only two are interesting: "win" and "run"

    What I need: count all "run" per line (that´s easy). Sometimes there is a "win" without a "run" before. This is a mistake and so I have to count it as there was a "run" before.
    So I neet a Function that counts the "win" per line ONLY if there is no "run" in the previous cell.

    I wrote CELL("col")-1 because I tried to reach the cell before but (of course) this doesn´t work.

    =ZÄHLENWENNS(Tabelle[[Spalte1]:[Spalte294]];"win";ZELLE("spalte")-1;NICHT("run"))

    EN:
    =COUNTIFS(Table([[Line1]:[Line294]]; "win"; CELL("col")-1; NOT("run"))

    Is something like this even possible in a function?


    Thanks for your help!

  2. #2
    Registered User
    Join Date
    07-19-2018
    Location
    Germany
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Count if previous element doesn´t have specific value

    I just found out by myself that you can reach the previous cell using =INDIRECT(ADDRESS(ROW(); COLUMN()-1)

    but this does not solve the problem. I think that´s because the reference to the first statement (cell with "win") is missing

  3. #3
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Count if previous element doesn´t have specific value

    I think it'd be easiest to add helper rows below each row and then run an =IF statement for cells containing "win" that do not have "run" in the previous cell. Then just count the number of hits you get.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Count if previous element doesn´t have specific value

    Please Login or Register  to view this content.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 07-02-2018, 12:49 PM
  2. [SOLVED] Comma separated string element count
    By icyrius in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2017, 10:58 AM
  3. Replies: 2
    Last Post: 09-25-2014, 02:22 PM
  4. How to count number of occurences of each element within array
    By cgkmal in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-05-2014, 04:33 PM
  5. [SOLVED] Macro Pivot Table: Most elegant way to skip a "do not display" if element doesn't exist
    By Skotzmun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2013, 05:03 AM
  6. count number of element
    By iscar_marius in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-19-2008, 10:45 AM
  7. UDF to reference a specific element in an array
    By GH in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-10-2005, 12:06 PM

Tags for this Thread

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