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.
<tbody>
</tbody>
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.
Call Sequence | Date | Call Type | Prefix | Suffix |
02211 | 30/05/16 | PRIMARY | PR | 0 |
02212 | 31/05/16 | PROCEDURE | PD | 0 |
02213 | 31/05/16 | TRANSFER | TF | 0 |
02214 | 01/06/16 | CASE FILE MX | MX | 0 |
02215 | 02/06/16 | ADMISSION | AD | 0 |
02216 | 02/06/16 | UNSERVICED- Cancelled | UNc | 0 |
02217 | 06/02/16 | UNSERVICED- Unaffordable | UNu | 0 |
02218 | 03/06/16 | UNSERVICED- Conveyed by other means | Uno | 0 |
02219 | 04/06/16 | UNSERVICED- No available ambulance | UNn | 0 |
02220 | 04/06/16 | ICU TRANSFER | ICUTF | 0 |
02221 | 04/06/16 | CASE FILE MX | MX | 0 |
<tbody>
</tbody>
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.