Forum Discussion
switch counts for each item
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
I interpreted your request slightly differently. The attached version uses a helper column on Sheet1.
I interpreted your request slightly differently. The attached version uses a helper column on Sheet1.
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
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.
