+ Reply to Thread
Results 1 to 8 of 8

Count 5 last rows for given product

  1. #1
    Registered User
    Join Date
    09-16-2018
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    4

    Count 5 last rows for given product

    Hello, I have the following problem. I am trying to calculate the table "from the bottom" (I do not mean one single result), so that the program has to search for a given product, eg code 1111 from column B and in the range of obtained results count how many defects have occurred marked with the letter "K" column C at 5 recent deliveries of this product from the bottom. I want to put a formula in column D. I tried with countifs and combined formula countif with if. This second solution was close
    = IF (COUNTIF (B29: B100; B29); COUNTIFS (B29: B36; B29; C29: C36; C29); 0) "However, in the second part, the formula counted from the cells found in the entire range and not only from those for the product 1111.
    The database is huge. The attached file is just a simplified example. Ideally, it would be if I could change the range of the last 10, a reference to the cell where I could freely change it. In the example, I just set the range in the formula for the last 5 lines.Produkt 1111 1.jpgProdukt 1111.jpg
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count 5 last rows for given product

    You could get the desired result with

    =COUNTIF(INDEX($C$2:$C$29,LARGE(IF($B$2:$B$29=1111,ROW($B$2:$B$29)-ROW($B$2)+1),5)):$C$29,"K")

    Array confirmed with Shift Ctrl Enter.

    Criteria can be set in other cells as needed by replacing the references with the cell holding the criteria.

    If you're constantly adding more data to the table then using a named table, or dynamic named ranges will mean you don't need to keep changing it.

  3. #3
    Registered User
    Join Date
    09-16-2018
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    4

    Re: Count 5 last rows for given product

    Hi, thanks for fast replay. The formula that You posted doesn't work in my exel and its so complicated for me that I don't know where is the problem. The other thing is that i see "$" so as I understand its anchored in bottom and this is not what i want to do. I will try to explain. I want to start with this formula in row 29 and paste it upword to d28, d27, d26. On bottom of my tabel are past orders and i will ad new ones on top. The real data base have 10000 entries and 1500 products. So if i have 10000 entries i will use it 10000 times and I would like to it to preform 10000 individual calculations. Error.jpg

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count 5 last rows for given product

    In that case, I think we might need a different approach.

    Using that formula 10000 times is going to take a lot of processing power

    I'll give it some thought to see if I can come up with something simple, but I think any solution is going to need some complex arrays that will take a lot of processing, inserting new data at the top will increase that processing requirement significantly. It would be better if you could invert your table and add new data at the bottom instead.

    To put it into perspective, adding a new row to the top of 10000 rows of data means the whole table has to be recalculated, not just the new entry.

    With your requirement of dragging the formula up, that would mean that 1 new entry means 50 million records to process (10000 + 9999 + 9998 + etc).

    If you add a new row to the bottom, then it will only need to process that set of 10000 records.
    Last edited by jason.b75; 09-16-2018 at 05:40 AM.

  5. #5
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count 5 last rows for given product

    See if this helps, I've done it with new rows at the top, but I think that it is going to be slow to calculate. Last 5 is controlled by the number entered in E1.

    =COUNTIFS(INDEX(B$2:B2,IFERROR(LARGE(IF(B$2:B2=B2,ROW(B$2:B2)-ROW(B$2)+1),$E$1),1)):B2,B2,INDEX(C$2:C2,IFERROR(LARGE(IF(B$2:B2=B2,ROW(B$2:B2)-ROW(B$2)+1),$E$1),1)):C2,"K")

    The first formula would have given incorrect results, I realised after that it was counting K's for all items, not just the specified one. This one works correctly.

    I think that the problem you had entering the formula, might be down to reginal settings so I've attached the sample file with the formula already in place, this will convert it to the correct settings for you when you open it.

    Enter the formula in D2 and fill down instead of filling up from the bottom.
    Attached Files Attached Files
    Last edited by jason.b75; 09-16-2018 at 06:07 AM.

  6. #6
    Registered User
    Join Date
    09-16-2018
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    4

    Re: Count 5 last rows for given product

    That is exactly what I need :D I want to cry from happiness I seek for solution for days :D I hope that I will figure it how it works and implement it form my data base properly. You gave me a lot of fun in next days. Big Thanks

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Count 5 last rows for given product

    I just had something simple hit me Much less effort required for a 2 column approach.

    In E2 and fill down

    =COUNTIF(B$2:B2,B2)

    In D2 and fill down

    =COUNTIFS(B$2:B2,B2,C$2:C2,"K",E$2:E2,">"&E2-$E$1)

    Still with 5 in E1 referring to the count of last 5.

    If you get the same error as last time when trying to enter the formula, try changing , to ; and see if that works.

  8. #8
    Registered User
    Join Date
    09-16-2018
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    4

    Re: Count 5 last rows for given product

    This work as well Thanks for help

+ 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. Count ifs criteria or sum product
    By geliedee in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-23-2015, 06:54 AM
  2. [SOLVED] count if with sum product
    By lejanco in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-23-2014, 10:07 PM
  3. Count if, sum product, dcount, not sure which i need or how to use it
    By mrproject44 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 11:05 AM
  4. Count if/Sum if/ or Sum product
    By a1b2c3d4e5f6g7 in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 12-04-2012, 03:46 PM
  5. Sum Product or Count If from Another File
    By mycon73 in forum Excel General
    Replies: 9
    Last Post: 10-15-2012, 07:15 AM
  6. Count Product
    By impulse03 in forum Excel General
    Replies: 2
    Last Post: 12-03-2011, 01:11 PM
  7. Replies: 2
    Last Post: 04-02-2009, 05:56 AM

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