Using a COUNTIFS formula with a less-than criteria

nj167252

New Member
Joined
May 10, 2016
Messages
4
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.
 
For < or > operations, the criteria needs to be written like

"<="&[@[Call Sequence]]
 
Last edited:
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top