-3
Category A  code        
111 rej     
111 ok      
111 ok      
222 ok      
222         
222 ok      
222 ok      
222 ok      
33  ok      
33  ok      
33  ok      
33  rej     
33  ok

Answer: count updated (bottom to top) 

111 - 2

222 - 3

33  - 1
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Nithesh
  • 1
  • 2
  • 1
    Welcome to StackOverflow... Please read [How do I ask a good question](https://stackoverflow.com/help/how-to-ask) then update your post. A little more dialogue about what is going on currently, what **you have tried** (including your code), and what is desired by your code. – Cyril Apr 22 '19 at 14:04
  • I use Vlookup and countif formula to count but I don't know how to stop for the change in value and move to next category – Nithesh Apr 22 '19 at 14:16
  • Welcome to Stack Overflow! Here's some tips on how to create a [mcve]. Also perhaps [this](https://stackoverflow.com/q/20625287/8112776) will be of help with your issue. – ashleedawg Apr 22 '19 at 14:22
  • 1
    @Nithesh Please include that in your post, including your formulas. Have you attempted any VBA for this? It sounds like you want to have a `do until` loop. – Cyril Apr 22 '19 at 14:31
  • I just started learning VBA, beginner. – Nithesh Apr 22 '19 at 14:33
  • Start at https://learn.microsoft.com/en-us/office/vba/api/overview/excel and look through https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/looping-through-a-range-of-cells -- it will take a while, but once you try to code a VBA loop, if still having problems, come back and add your VBA code to your question. – Mark Stewart Apr 22 '19 at 15:31

1 Answers1

2

To accomplish this with excel formulas, here's one way to do it.

Col D

Columns A and B contain your original data. Columns D and E are the original data in reverse order. Excel formulas generally work from the top down, instead of the bottom up. Reversing the original data makes it easier to use the formulas that follow. The formula for doing the reversing counts how many rows are left in the original data (ROWS(A2:$A$14)) and uses that as an index to the reversed value. So for D2 there are 13 rows (ROWS(A2:$A$14)), using INDEX that gets us A14. For D3 there are 12 rows (ROWS(A3:$A$14)), using INDEX that gets us A13...

Col F

Column F is where most of the work happens. The COUNTIFS is counting the number of rows that satisfy 2 conditions up to that point. The first condition is that the row must be equal to the category we're examining (so for F2 this would be D2 (33), for F8 this would be D8 (222)). The second condition is that the code is not equal to "ok". So for example the formula in F5 is counting the number of rows, between D2:D5 with a category of 33 and a code != "ok", the result is 1 (E3 is the only one).

Col I

To get the final counts we count the number of rows equal to the category we're counting, that have a count (Col F value) equal to zero.

gunnerone
  • 3,566
  • 2
  • 14
  • 18