Countifs problem

[ToniShinobi]

Hello,

I'm somewhat new to Excel (used it before, but not heavily) but used to the concept of functions (I have written programs before). I'm trying to Count the number of Entries on a Sheet that match an Hour. Looking through the availiable functions i found COUNTIFS, which is exactly what I want. However, when I try to compare the Hour values within the COUNTIFS arguments, there is an error.

This is the function that I figured would work here:
=COUNTIFS(HOUR(Sheet1!G:G), HOUR(E6))

which should count all entries in column G where its HOUR matches the HOUR in E6 (all are time format). I do realize that in the example above there is only one comparison made and i'm using COUNTIFS instead of COUNTIF, but i'll be adding other comparisons to it once i get this first comparison working.

Help is appreciated!!!
- Toni

 

[Aladin Akyurek]

IT WORKS!

I tweaked your last code post since it was missing some parameters:

=SUM(
IF(ISNUMBER(cit!G:G),
IF(HOUR(cit!G:G)=HOUR(E5),
IF(cit!E:E=1,
1, 0), 0), 0))

When i pressed ctrl+shft+enter on the code posted, nothing happend since there were errors, but On this code it places curly braces around the function. What does that do?

Thanks so much for your time, i know this took alot of it!

I failed to drop some parens during the edit from SumProduct to Sum+If...

=SUM(
   IF(ISNUMBER(cit!G:G),
   IF(HOUR(cit!G:G)=HOUR(E6),
   IF(cit!E:E=1,
   IF(cit!F:F=1,
    1)))))

or without the test involving the F column...

=SUM(
   IF(ISNUMBER(cit!G:G),
   IF(HOUR(cit!G:G)=HOUR(E6),
   IF(cit!E:E=1,
    1))))

The curly braces indicate that you did apply control+shift+enter correctly. This key combination is used to signal to Excel that the formula in question must process array objects which I mentioned earlier when discussing CountIf and CountIfs. How about SumProduct? This function is programmed for processing array objects.