SOLVED

Iron Contributor

# 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 ,

Br,

Anupam

3 Replies
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: switch counts for each item

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

# Re: switch counts for each item

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

# Re: switch counts for each item

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.

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: switch counts for each item

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