Count the number of instance of 50% and 60% for each unit. If the unit appeared twice..

heyjay

I need help with the following. I want to count the number of instance of 50% and 60% for each unit. If the unit appeared twice, count only the most recent value based on the date.

The formula will be on the sheet summary, and I entered the expected result.

Amit Wadhwani

Hi @heyjay

This is fairly simple

1. Create a Auto Number column and name it "Auto". Set the starting number to 1, no prefix.
2. Create a text column and name it Instance. Insert below formula in the column. It will give a serial number to each repeated instance of _Unit.
   =COUNTIFS([_Unit]:[_Unit], @cell = [_Unit]@row, Auto:Auto, @cell <= Auto@row)

3. Create a text column and name it LastInstance. Insert below formula in the column. It will identify if the Instance is the last Instance for the specific _Unit.
   =IF(Instance@row = MAX(COLLECT(Instance:Instance, [_Unit]:[_Unit], [_Unit]@row)), "Yes", "No")
4. Finally, use this formula in the summary field for calculating the number of items in _Unit which as 50% as the last value (towards bottom).
   =COUNTIFS(LastInstance:LastInstance, "Yes", [%]:[%], 0.5)

Ensure that the % column is formatted as percentage. If it is not percentage. you need to change 0.5. Use 0.6 in the summary field for counting _Units with 60% as the latest % value.