I have a raw data set that lists items by their unique identification # in the first column (1 item per row), there is then a status, date and time in one cell for each "status" for the course of the item's life presented like this "F7 24MAY2012:00:47:40" (Status = F7, date = 24MAY2012 & Time = 00:47:40 (24 hour clock stamp)). I have a second sheet that breaks down the raw data by spliting the status and date from time using LEFT() and MID (), which reads out the hour, like:
Bcst_Statu _D Bcst_Statu _D
D2 19APR 20 D4 27APR 07
D2 19APR 20 D4 30APR 10
D2 30APR 17 D4 01MAY 06

This data reads from column "H" to "CE".

I want to be able to count status "GA" for first and second shift. (I.E. "GA 22MAY" from time "6-24" hour & "GA 23MAY" from time "0-4" and report out how many occur in between these times.) The other problem is that the "status" are not all in the same column, "GA" status can range from column N to AF in the Raw Data sheet.

I've tried:
=COUNTIFS('Refined Data'!$A$1:$CE$1731,D2,'Refined Data'!$A$1:$CE$1731,">=6")+COUNTIFS('Refined Data'!$A$1:$CE$1731,D3,'Refined Data'!$A$1:$CE$1731,"<=4")