Sumif a range based on multiple parameters

[ifthenelsenull]

I need to be able to calculate guests by hour by day only using start and end date and time.

I have been looking at countif and sumif but they seem to want to use the column to be counted in the compare. I want to count a different column than I am comparing. I need to count visitids where the date and time columns of data agree with the date and time of the created.

What I have done is create columns of hours from 0-23 and rows for each day.

Hours
Date 0:00 1:00 2:00 3:00 4:00 5:00 6:00 7:00 8:00 9:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00
6/5/2014
6/6/2014
6/7/2014
6/8/2014


The data I have looks like:
globalid visitid subvisitit visitstart_date VisitStarthour visitend_date VisitEndhour
1 99 999 6/5/2014 4:00 6/9/2014 4:00
2 100 1000 6/5/2014 6:00 6/10/2014 6:00
3 101 1001 6/6/2014 12:00 6/11/2014 12:00
4 102 1002 6/7/2014 2:00 6/12/2014 2:00
5 103 1003 6/7/2014 14:00 6/13/2014 14:00
6 104 1004 6/7/2014 18:00 6/14/2014 18:00
7 105 1005 6/8/2014 7:00 6/14/2014 7:00
8 106 1006 6/8/2014 8:00 6/14/2014 8:00
9 107 1007 6/8/2014 9:00 6/14/2014 9:00
10 108 1008 6/9/2014 10:00 6/14/2014 10:00

What I need to do is count the range of visitid

if

visitstart_date > = the date in the row
and
visitstarthour <= the hour in the column
and
visitend_date > = date in the row
and
VisitEndhour > = the hour in the column

datacalctest.xlsx

I have uploaded the workbook. The data I need is on the worksheet wanted.

I am trying to figure out how to do this programatically so I can pull my historical data out.

[Richard Buttrey]

Hi and welcome to the forum

Have you tried COUNTIFS() or SUMIFS() - note the plural (first introduced in XL 2007)

[gak67]

See if this works for you. It uses COUNTIFS as Richard suggested.
datacalctest.xlsx

[ifthenelsenull]

You were exactly right with the plurals. Thank you.

[ifthenelsenull]

Thank you very much for the code example. I was wondering how to tie them together. I really appreciate the work.

[gak67]

What do you mean by tie them together? What is the end goal? What are you trying to achieve?

[JR DHONA]

hi
ifthenelsenull,

please check,

hoped can meet with your expectations.
so, you can check guests by hour and by day,
without create columns of hours from 0-23 and rows for each day.

regards,