Count by date and hour

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.

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)

Thanks very much!

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

=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

--
Kevin Vaughn


"ChristiaanV" wrote:

>
> Out of curiosity, what exactly are the double dashes, and what do they
> do?
>
>
> --
> ChristiaanV
> ------------------------------------------------------------------------
> ChristiaanV's Profile: http://www.excelforum.com/member.php...o&userid=37363
> View this thread: http://www.excelforum.com/showthread...hreadid=570571
>
>