Count by date and hour

[ChristiaanV]

Hi. I have a downloadable report that shows individual transactions. For
each transaction there is an entry in the "Date" column and in the
"Time" column. I have created a second sheet to record the number of
transactions by date and hour (i.e. July 1 at 10 am, 11 am, etc., July
2 at 10 am, etc.).

If I create an additional column in the original report worksheet
titled "HourData" and use the Hour worksheet function on the time
column, I have no problem using an array formula like
{=sum((HourData=10)*(Date=07/01/06))} to count only instances of a
particular date and hour. Or, I can use a pivot table and group the
times by hour.

However, I'd like to skip the creation of the "HourData" column or
creating a pivot table. Is there any way I can specify that I want to
compare only the "hour" information in the "Time" column to my defined
criteria. This even goes to simple Countif and Sumif functions. How can
I count or sum only specific hours from a range of times?

Thanks.

 

[daddylonglegs]

If your dates are in column A and times in column B

=SUMPRODUCT(--(A1:A100="07/01/06"+0),--(HOUR(B1:B100)=10))

for a count.

to sum column C where these criteria apply

=SUMPRODUCT(--(A1:A100="07/01/06"+0),--(HOUR(B1:B100)=10),C1:C100

 

[ChristiaanV]

Thanks very much!

 

[ChristiaanV]

Out of curiosity, what exactly are the double dashes, and what do the
do

 

[Guest]

=SUMPRODUCT(--(A1:A100="07/01/06"+0),--(HOUR(B1:B100)=10))
Sumproduct likes to work with numbers, but the tests above will return
logical values, either True or False. -- will turn the logical values into
numbers, either 1 for true or 0 for false. The first - will make a true -1.
The 2nd will turn the negative 1 to a positive 1. Negating false returns 0
(both times.)

Bob Phillips has a very useful explanation of Sumproduct. Let me dig up the
link ...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Also, this link explains the --:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html