# counting with multiple variables

#### Veganut

##### New Member
I'm a newbie and would like the assistance of the Guru's that frequent this board.
For each day of the week ,I would like to count the number of times for each hour on each day. ie. Sun @ 0hr(12a),@1hr,@2hr... and the same for Mon.,Tue.,Wed.,... p.s. these spreadsheets are 40k to60k rows deep.

Day of Wk.....Date........Time
Fri..............5/5/2006.....0:02
Thu...........5/11/2006.....0:04
Sun............5/7/2006.....0:06
Tue...........5/30/2006.....0:08
Thu.............5/4/2006.....0:10
Mon.............5/1/2006.....0:12
Sat............5/13/2006.....0:14
Mon...........5/15/2006.....0:16
Sun...........5/28/2006.....0:20
Fri...............5/5/2006.....0:22
Tue...........5/30/2006.....0:24
Sat............5/20/2006.....0:42
Mon...........5/22/2006.....0:46
Tue.............5/9/2006.....0:54
Sat..............5/6/2006.....0:56
Wed............5/3/2006.....1:00
Mon............5/1/2006.....1:00
Thu...........5/18/2006.....1:30
Mon............5/1/2006.....1:30
Thu...........5/18/2006.....1:58
Sun...........5/14/2006.....2:10
Sat.............5/6/2006.....2:36
Tue...........5/16/2006.....3:24
Sat............5/20/2006.....3:30
Mon...........5/15/2006.....7:32
Wed...........5/17/2006.....11:04
Thanks!

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi, welcome to the board!

I would like to count the number of times for each hour on each day.

What does that mean exactly?

Hi, In the example data the dots just indicates that the data is in separate columns.
I want to count non-empty cells in the Day of Wk column when the Day of Wk is "Sun" AND the Time is equal to any time during any particular hour,.
ie. 0:00 to 0:59 or 1:00 to 1:59.. the example shows that "Mon" occurs three times during the "0" hour, so the result would be:
Day of Wk......Hour..... Occurances
Mon............... 0.............3
Mon............... 1.............2
Mon............... 2.............0
Mon............... 7.............1
Day of Wk is derived from the function =Weekday("value in Date column")and the cells formatted with custom "ddd".

One caveate is that if the date column is empty the function for "Day of Wk" cells returns a 7 or Sat. which would make the count for Sat invalid.

For the day of week, use =TEXT(range,"ddd")
Can you add an extra column, say column B, that is =HOUR(D1)
then use a Pivot Table?
Book2
ABCD
1Day of WkHourDateTime
2Fri05/5/20060:02
3Thu05/11/20060:04
4Sun05/7/20060:06
5Tue05/30/20060:08
6Thu05/4/20060:10
7Mon05/1/20060:12
8Sat05/13/20060:14
9Mon05/15/20060:16
10Sun05/28/20060:20
11Fri05/5/20060:22
12Tue05/30/20060:24
13Sat05/20/20060:42
14Mon05/22/20060:46
15Tue05/9/20060:54
16Sat05/6/20060:56
17Wed15/3/20061:00
18Mon15/1/20061:00
19Thu15/18/20061:30
20Mon15/1/20061:30
21Thu15/18/20061:58
22Sun25/14/20062:10
23Sat25/6/20062:36
24Tue35/16/20063:24
25Sat35/20/20063:30
26Mon75/15/20067:32
27Wed115/17/200611:04
Sheet1

Looks great but I don't know Pivot Tables. I'll look for explanation in Excel help.

Thanks A LOT for the suggestions.

Replies
5
Views
241
Replies
4
Views
413
Replies
0
Views
116
Replies
9
Views
238
Replies
5
Views
275

1,219,808
Messages
6,150,351
Members
450,952
Latest member
Zung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back