COUNTIF-type loop with multiple logic requirements?

Originally Posted by reagent

Suppose I have a spreadsheet with 2 columns:

Column A - Date
Column B - Time

Each row then is an entry for an incoming call. I'll get these spreadsheets everyday so its a variable amount of rows depending on how many calls came in on that day.

What I'm trying to do is make a graph that breaks down call frequency by 1-hr intervals for various days. So what I need is a table with a list of dates going vertically and a list of time intervals going horizontally (eg. 10-11AM, 11AM-12PM and so forth). Then under each time I need to know how many calls came in during that interval on that date.

I can subtract two COUNTIF statements from each other to find the total amount of calls coming in for that interval, but then I can't break it down by date.

=COUNTIF('I:\Users\Matt\Desktop\[data.csv]data'!$D$2:$D$477,D8">=10:30 AM")

There is an example of the countif I was using for individual day graphs. But now I need a macro to create this graph for multiple dates.

I've got a method for filling a column with all the dates contained (dump the raw data to a pivot table and pick out the dates then fill down a column with this:

=IF(OR(Sheet1!A6="(blank)",Sheet1!A6="Grand Total",Sheet1!A6=0),"",Sheet1!A6)

So far column X shows rows of dates. Columns Y Z and AA are "10AM-11" "11-12" "12-1" respectively. I need a loop or something to read amount of calls made on the date in column X between the time in column Y.

Any ideas would be very helpful as I'm stuck! Thanks

Hi,

To solve this problem you will have to do a bit of work.
The way I see it you have 2 possible solutions.

Regardless of the solution the first step for any of the 2 is the following:

Time formats in excel are just numbers stored in... time format.
Bellow is the number equivalent for the 24 hours in excel:

Hour Number equivalent
00:00 0,0000000
01:00 0,0416667
02:00 0,0833333
03:00 0,1250000
04:00 0,1666667
05:00 0,2083333
06:00 0,2500000
07:00 0,2916667
08:00 0,3333333
09:00 0,3750000
10:00 0,4166667
11:00 0,4583333
12:00 0,5000000
13:00 0,5416667
14:00 0,5833333
15:00 0,6250000
16:00 0,6666667
17:00 0,7083333
18:00 0,7500000
19:00 0,7916667
20:00 0,8333333
21:00 0,8750000
22:00 0,9166667
23:00 0,9583333

Save this list in a new sheet. Lets call it "Sheet4" for demo purposes. Save it in A1 to B25 of sheet4.

In the sheet where you have your data. Considering your dates start in A2 (A1 being the heading), your CORRECT time format in B2 (B1 being the heading), insert headings in cells C1,D1,E1,
Lets call them "9-10Am" "10-11Am" and "11-12Pm" respectivelly.

Now for the interesting part:
In cell C2 insert the following formula:

=IF(AND($B2>=Sheet4!$B$10;$B2<Sheet4!$B$11);1;"")
(B10 in sheet 4 corresponds to 0,3750000 and B11 to 0,4166667) - Basically: If date in cellB2 >=9am AND <10PM then "1" otherwise blank.

You can drag down this formula to all rows

In Cell C2 insert the formula:
=If(And($B2>=Sheet4!$B$11;$B2<Sheet4!$B$12);1;"")
Basically: If date in cellB2 >=10am AND <11PM then "1" otherwise blank.

In D2:
=If(And($B2>=Sheet4!$B$12;$B2<Sheet4!$B$13);1;"")

etc

You will get something like this:

Date____________Time_______ 9am - 10______10 am - 11_______11 am - 12
15/02/2008_______09:30_______1
16/08/2006_______09:30_______1
16/05/2008_______11:30________________________________________1
15/02/2008_______09:30_______1
16/08/2006_______10:30______________________1
16/05/2008_______11:30_________________________________________1
15/02/2008_______09:30_______1

Now you have 2 options:

1- Apply pivot tables to the data. You will need to create a pivot for each time period. where "date" stays in the row section. "Count of time" in the data section. Hourly Period in column section. With someColumn and row hiding, you can get what you want.


2-Option 2. Use an array formula.

First In Sheet4 you need to create a unique list of dates. (Better if ascending) Lets say in Cell C1 of sheet 4 you name it "Unique date list", and in D1,E1,F1 the headings "9-10Am" "10-11Am" "11-12Pm" respectivelly.

For demo purposes in C2 you Start with date 01/01/2008 and drag down to whenever.

In Cell D2 - corresponding to the header "9-10Am" Insert the following formula:
=COUNT(IF(SHEET1!$A$2:$A$5000=C2;IF(SHEET1!$C$2:$C$5000=1;$C$2:$C$5000)))

Basically: Count if in column A of Sheet1 (where you have the data) there is the date of cell C2 (C2 being the Date) AND in column C of Sheet1 you have 1.

Now, this formula will give you wrong info if you dont have it as an array formula. To do this, click inside the cell (D2)and use the comand Ctrl+shift+enter.

You will see the result change and the { } characters will wrap the formula.

{=COUNT(IF(SHEET1!$A$2:$A$5000=C2;SI(SHEET1!$C$2:$C$5000=1;$C$2:$C$5000)))}

You will have something like this:
(ColumnC) _______(ColumnD)
Unique Date_______9-10Am
01/01/2008_______ 1
02/01/2008________2
03/01/2008________3
04/01/2008________15

You can apply a pivot table to this list...


I go now, I´m tired.

bhandge88

Welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.