Counting with multiple conditions

**BradDenton** · 05-29-2012, 06:08 PM

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")

**xladept** · 05-29-2012, 07:21 PM

Hi BradDenton,

Could you post a sample sheet? And, are you amenable to a User Defined Function (UDF)?

**BradDenton** · 05-29-2012, 08:04 PM

Raw Data: Sample Data File.xlsx

**xladept** · 05-30-2012, 05:20 PM

Hi BradDenton,

I got 421 with your criteria - does that sound right? If so, I'll try to make the macro into a function.

**xladept** · 05-30-2012, 05:24 PM

Hi,

Make that 1765 hits.

**BradDenton** · 05-30-2012, 06:46 PM

If counting for "GA 22MAY" between hours of 6am-midnight and "GA 23MAY" from Midnight - 4am it should come up with only 24.

In this instance there are none on 23MAY between midnight - 4am.

If you try to count GA status for 23MAY 6-mid and 24MAY Mid-4am you should get 77

**xladept** · 05-30-2012, 09:58 PM

Hi BradDenton,

Got it! 24 and 77 - I'll try to make it be a function - Do you want the macro?

**BradDenton** · 05-30-2012, 10:21 PM

Yes the macro would be great. I am very interested to see how you got it running!

**xladept** · 05-31-2012, 03:37 PM

Hi BradDenton,

I lost the macro but the function is up and working. Here's a copy of your book - I didn't paste all the fields, just a few at the beginning and the end. The trick was in using the date #......# entry.