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

9592 - For Road Service

##9#9 criteria 2

9592 - For Road Service

900 criteria 2

9592 - For Road Service

0 criteria 2

9592 - For Road Service

9 criteria 2

9592 - For Road Service

99 criteria 2

9503 - To become a member

#0 criteria 2

9503 - To become a member

90 criteria 2

9592 - For Road Service

9592 - For Road Service

9 criteria 2

9592 - For Road Service

9592 - For Road Service

9592 - For Road Service

9592 - For Road Service

<colgroup><col></colgroup><tbody>
</tbody>

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