Grouping Multi-Period

Hi,

I have some data in an excel file with the following attributes (among many others): There are names that are linked to a rating (ratings are Below target, At Target or Above Target) based on sales performance over four periods (Period 1 to Period 4). The rating most likely occurs in alternating periods (Period 1 then 3 or 2 then 4 but may happen in, consecutive periods. I've been tasked with grouping the names in the following manner: Always below target, Moved from above target rating to Below Target).

I've been tearing my hair out using a pivot table then copying and pasting that data and applying nested conditional IF statements but It doesn't seem to be working.

Any help would be really appreciated.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

As you are using the 365 version of Excel you could use Get & Transform to put the data into a proper row over row arrangement as modeled in columns A:C on Sheet 2 using the following Advanced Editor code:

Please Login or Register  to view this content.

Then three helper columns could be added to the resulting table:
The first helper column displays the employees who always have a rating of "Below Target" using: =IF(COUNTIFS(A$2:A2,A2)=1,COUNTIFS([[Employee ]],[@[Employee ]],[Rating],"Below Target")=COUNTIFS([[Employee ]],[@[Employee ]]),"")
The second helper column displays the employees whose rating fell from "Above Target to Below Target" using: =AND(A2=A1,C2="Below Target",C1="AboveTarget")
The third helper column displays the employees whose rating fell from "At Target to Below Target" using: =AND(A2=A1,C2="Below Target",C1="At Target")
The count of employees uses: =SUMPRODUCT(--(Table1_2[ABT]<>""))
The count of employees who always have a rating of "Below Target" uses: =COUNTIFS(Table1_2[ABT],TRUE)
The count of employees whose rating fell from "Above Target to Below Target" uses: =COUNTIFS(Table1_2[AbT to BT],TRUE)
The count of employees whose rating fell from "At Target to Below Target" uses: =COUNTIFS(Table1_2[AtT to BT],TRUE)
Let us know if you have any questions.