switch counts for each item

[anupambit1797]

Dear Experts,

                   I have a Data (as attached ),

In column "D", we have item name as "rnti" and each rnti can have switch in ueBwpId (from 0>1>2),

in sheet2, I want to make a summary for each rnti how many switches happened example as below

for rnti = [28559] in column "E", should have count of all the switches from ueBwpId from 2->1 ,

Thanks in Advance,

Br,

Anupam

[#2 · Accepted solution]

@anupambit1797 

I interpreted your request slightly differently. The attached version uses a helper column on Sheet1.

[#3]

Thanks a lot @HansVogelaar , this is exactly what I wanted, but could you please educate me on this "helper column"?

What does this Column "F" in sheet1 does? if you can help explain the criteria you used?

Thanks again 🙂

Br,

Anupam

[#4]

@anupambit1797 

The formula in F587 is

=LET(r, MAX(IF($D$2:$D586=$D587, ROW($D$2:$D586))), p, IF(r=0, "", INDEX($E$2:$E586, r-1)), IF(OR(p="", p=E587), "", p&">"&E587))

r = MAX(IF($D$2:$D586=$D587, ROW($D$2:$D586))) calculates the highest row number of a cell in column D above D587 with the same rnti as D587. If there is no such row, r = 0.

p = IF(r=0, "", INDEX($E$2:$E586, r-1)) returns "" if r = 0, otherwise it returns the ueBpwld of that row, i.e. the previous ueBpwld.

Finally, the formula returns IF(OR(p="", p=E587), "", p&">"&E587).

If the previous ueBpwld is blank or equal to the current ueBpwld in E587(i.e. no switch), it returns a blank. Otherwise, it concatenates the previous and current ueBpwld with ">" in between, i.e. the switch.

The formulas on the second sheet simply use COUNTIFS to count the number of switches for the rnti in column A.