Count if Hour in multiple columns

[Gusch]

I have multiple columns (not next to each other) with times in them (13:45,14:00). I would like to be able count the number of times any given hour shows up. I can do with sumproduct for one column, but not multiple columns.

Thx - Ryan

 

[mikerickson]

What formula are you using for the one column?

You might be able to just expand the range since
=SUMPRODUCT(--(A1:C10)="z") will return how many cells in A1:C10 contain "z".

For non-continous ranges, if the intervenening columns don't contain dates, their inclusion won't effect anything.

 

[Gusch]

I have multiple columns with times and only need certian ones. I've tried adjusting the range by holding "ctl", but I'm getting 0 back in return

 

[mikerickson]

What formula are you using for one column?

 

[Gusch]

=SUMPRODUCT(--(HOUR($G$4:$G$21)=9))

Other columns are L, Q, AA etc.

 

[mikerickson]

Try
=SUMPRODUCT(SIGN( (HOUR($G$4:$G$21)=9) + (HOUR($L$4:$L$21)=9) = (HOUR($Q$4:$Q$21)=9) + (HOUR($AA$4:$AA$21)=9) ))
to count rows.
Or
=SUMPRODUCT( (HOUR($G$4:$G$21)=9) + (HOUR($L$4:$L$21)=9) = (HOUR($Q$4:$Q$21)=9) + (HOUR($AA$4:$AA$21)=9) )
to count cells.

 

[Gusch]

the 2nd one worked - thanks