Need to create a formula to count cells between 2 numbers in one column and all cells in another column based on filtering criteria

[queno]

Hi,

I need to create a formula to count cells between 2 numbers in one column and all cells in another column based on filtering criteria.

Column M (Time in secs) has number values
Column AD - (IVR Selection labels) has both numbers and text

Example IVR Selection label from column AD: 9592 - For Road Service

Example of count needed:
(1st criteria - Column M) Provide a single count for all calls(cells) above 90 seconds, (2nd criteria - Column AD) which do not begin with 4 numbers, space, dash, space e.g. "9592 - " the count should also include "blank cells".

Essentially I am just wanting the second criteria to filter and count all calls that don't begin with 4 numbers, space, dash, space so I can know the the % of calls with no IVR option recorded against total calls


Here is a sample of the cells from column AD

[TABLE="width: 432"]
<colgroup><col></colgroup><tbody>[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]##9#9 criteria 2[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]900 criteria 2[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]0 criteria 2[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]9 criteria 2[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]99 criteria 2[/TD]
[/TR]
[TR]
[TD]9503 - To become a member[/TD]
[/TR]
[TR]
[TD]#0 criteria 2[/TD]
[/TR]
[TR]
[TD]9503 - To become a member[/TD]
[/TR]
[TR]
[TD]90 criteria 2[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]9 criteria 2[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
[TR]
[TD]9592 - For Road Service[/TD]
[/TR]
</tbody>[/TABLE]
blank cell criteria 2


Appreciate any help to create the required formula.


Thanks
Matt

 

[baitmaster]

welcome to the board

I haven't quite grasped your requirements. You've mentioned "between 2 numbers" then said everything above 90 seconds?

And are you after 2 separate formulas, or 1 formula?

For requirement 2 alone, an array formula can count any cells containing " - " as characters 5-7. Assuming your data is in cells A1:A20, the following formula would work (entered using Shift + Ctrl + Enter, not just Enter):
=SUM(IF(MID(A1:A20,5,3)<>" - ",1,0))
This doesn't currently count empty cells but that's easy enough once the rest of the requirement is clear