Below is the table I am working with. The "Call Sequence" column is just a continuous list of Calls (1 to 2221). There are 11 different call types with 11 corresponding Prefixes. Because there are many calls of the same Call Type I would like to count them. I also want to have the "Suffix" represent the latest total number of that specific Call Type.
[TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]Call Sequence[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Call Type[/TD]
[TD="align: center"]Prefix[/TD]
[TD="align: center"]Suffix[/TD]
[/TR]
[TR]
[TD="align: center"]02211[/TD]
[TD="align: center"]30/05/16[/TD]
[TD="align: center"]PRIMARY[/TD]
[TD="align: center"]PR[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02212[/TD]
[TD="align: center"]31/05/16[/TD]
[TD="align: center"]PROCEDURE[/TD]
[TD="align: center"]PD[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02213[/TD]
[TD="align: center"]31/05/16[/TD]
[TD="align: center"]TRANSFER[/TD]
[TD="align: center"]TF[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02214[/TD]
[TD="align: center"]01/06/16[/TD]
[TD="align: center"]CASE FILE MX[/TD]
[TD="align: center"]MX[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02215[/TD]
[TD="align: center"]02/06/16[/TD]
[TD="align: center"]ADMISSION[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02216[/TD]
[TD="align: center"]02/06/16[/TD]
[TD="align: center"]UNSERVICED- Cancelled[/TD]
[TD="align: center"]UNc[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02217[/TD]
[TD="align: center"]06/02/16[/TD]
[TD="align: center"]UNSERVICED- Unaffordable[/TD]
[TD="align: center"]UNu[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02218[/TD]
[TD="align: center"]03/06/16[/TD]
[TD="align: center"]UNSERVICED- Conveyed by other means[/TD]
[TD="align: center"]Uno[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02219[/TD]
[TD="align: center"]04/06/16[/TD]
[TD="align: center"]UNSERVICED- No available ambulance[/TD]
[TD="align: center"]UNn[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02220[/TD]
[TD="align: center"]04/06/16[/TD]
[TD="align: center"]ICU TRANSFER[/TD]
[TD="align: center"]ICUTF[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02221[/TD]
[TD="align: center"]04/06/16[/TD]
[TD="align: center"]CASE FILE MX[/TD]
[TD="align: center"]MX[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
So, for example, row 02214 has a Prefix "MX," I want the suffix of that row to show "001." And on row 02221, which also has a Prefix "MX," that suffix should show "002." When I use a COUNTIFS formula (=COUNTIFS([Prefix], [@Prefix],[Call Sequence], "<=[@[Call Sequence]]"), it shows "0" as the result.
Any help is much appreciated.
[TABLE="width: 459"]
<tbody>[TR]
[TD="align: center"]Call Sequence[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Call Type[/TD]
[TD="align: center"]Prefix[/TD]
[TD="align: center"]Suffix[/TD]
[/TR]
[TR]
[TD="align: center"]02211[/TD]
[TD="align: center"]30/05/16[/TD]
[TD="align: center"]PRIMARY[/TD]
[TD="align: center"]PR[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02212[/TD]
[TD="align: center"]31/05/16[/TD]
[TD="align: center"]PROCEDURE[/TD]
[TD="align: center"]PD[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02213[/TD]
[TD="align: center"]31/05/16[/TD]
[TD="align: center"]TRANSFER[/TD]
[TD="align: center"]TF[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02214[/TD]
[TD="align: center"]01/06/16[/TD]
[TD="align: center"]CASE FILE MX[/TD]
[TD="align: center"]MX[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02215[/TD]
[TD="align: center"]02/06/16[/TD]
[TD="align: center"]ADMISSION[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02216[/TD]
[TD="align: center"]02/06/16[/TD]
[TD="align: center"]UNSERVICED- Cancelled[/TD]
[TD="align: center"]UNc[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02217[/TD]
[TD="align: center"]06/02/16[/TD]
[TD="align: center"]UNSERVICED- Unaffordable[/TD]
[TD="align: center"]UNu[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02218[/TD]
[TD="align: center"]03/06/16[/TD]
[TD="align: center"]UNSERVICED- Conveyed by other means[/TD]
[TD="align: center"]Uno[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02219[/TD]
[TD="align: center"]04/06/16[/TD]
[TD="align: center"]UNSERVICED- No available ambulance[/TD]
[TD="align: center"]UNn[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02220[/TD]
[TD="align: center"]04/06/16[/TD]
[TD="align: center"]ICU TRANSFER[/TD]
[TD="align: center"]ICUTF[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]02221[/TD]
[TD="align: center"]04/06/16[/TD]
[TD="align: center"]CASE FILE MX[/TD]
[TD="align: center"]MX[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]
So, for example, row 02214 has a Prefix "MX," I want the suffix of that row to show "001." And on row 02221, which also has a Prefix "MX," that suffix should show "002." When I use a COUNTIFS formula (=COUNTIFS([Prefix], [@Prefix],[Call Sequence], "<=[@[Call Sequence]]"), it shows "0" as the result.
Any help is much appreciated.