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]

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

Try...

=SUMPRODUCT((HOUR(Sheet1!G:G)=HOUR(E6))+0)

instead.

 

[ToniShinobi]

How does that work? I got it to work with a little editing, because the column G has a title, so using G:G would return #VALUE!

Why does that work and COUNTIF doesn't?

 

[ToniShinobi]

Sorry for double post, but I need to know if it's possible to get it to work with COUNTIFS because I need to check some other criteria as well before counting. Also, Sumproduct does the job if i specify to start at G2:G<last entry> but if I add more entries i'd have to change where i call the function, unless I get rid of the title of the column in G1.

 

[Aladin Akyurek]

Sorry for double post, but I need to know if it's possible to get it to work with COUNTIFS because I need to check some other criteria as well before counting. Also, Sumproduct does the job if i specify to start at G2:G<LAST entry> but if I add more entries i'd have to change where i call the function, unless I get rid of the title of the column in G1.

=SUMPRODUCT(
   --ISNUMBER(Sheet1!G:G),
   --(HOUR(Sheet1!G:G)=HOUR(E6)))

Or better, convert the current range into a table with Tables|Table, then just invoke, say:

=SUMPRODUCT((HOUR(Sheet1!G2:G400)=HOUR(E6))+0)

Replace G2:G400 with the current range.

COUNTIFS just like COUNTIF does not admit array objects.

 

[ToniShinobi]

Thank you so much for your time. I'm just confused over how
=COUNTIF(cit!E:E,1) works, but
=COUNTIF(HOUR(cit!E:E),1) does not. if there was a way to convert the return type of HOUR into the correct format that would be grand.

 

[Aladin Akyurek]

Thank you so much for your time. I'm just confused over how
=COUNTIF(cit!E:E,1) works, but
=COUNTIF(HOUR(cit!E:E),1) does not. if there was a way to convert the return type of HOUR into the correct format that would be grand.

HOUR(cit!E:E) yields an array object something like {1;2;...} CountIf cannot cope with... It's designed to work only with range objects like cit!E:E...

Why not evaluate the suggestions I made...

 

[ToniShinobi]

Oh i'm sorry. I did evaluate the first suggestion you made. It seems like it should work, but i'm getting #VALUE! back. I didn't understand what you meant with Tables|Table so i was unable to try that one. I'm tweaking the first one to see if I can get the right formula using the same SUMPRODUCT strategy. I was persistant on COUNTIFS because I also need to check other columns to see if values were 1 or 0, but with the SUMPRODUCT method it should theoretically work

I think the reason why the first method didn't work is because the column G is Date and Time format. so it may not be considered Numeric. I'm trying to get the HOUR from that date and time and compare it to E6.

 

[Aladin Akyurek]

Oh i'm sorry. I did evaluate the first suggestion you made. It seems like it should work, but i'm getting #VALUE! back. I didn't understand what you meant with Tables|Table so i was unable to try that one. I'm tweaking the first one to see if I can get the right formula using the same SUMPRODUCT strategy. I was persistant on COUNTIFS because I also need to check other columns to see if values were 1 or 0, but with the SUMPRODUCT method it should theoretically work

I think the reason why the first method didn't work is because the column G is Date and Time format. so it may not be considered Numeric. I'm trying to get the HOUR from that date and time and compare it to E6.

A date/time if properly entered is a numeric value...

Would you post some (5, say) values from column G and what you have in E6?

Tables|Table is an option (a command)...

 

[ToniShinobi]

Here is what's going on: There are two sheets. "cit" has the bulk data that i want to process. "statistics" is the sheet holding the report i'm creating.

in "cit" column E holds 0's and 1's telling me if the data is "void"
in "cit" column F holds 0's and 1's telling me that the record was a "warning"
in "cit" column G holds dates, in proper time and date format, of when the record was processed. Example data:
7/2/2007 0:00
7/2/2007 8:30
7/2/2007 9:00
etc.
all columns in "cit" have a title in the first row. This is where the problems with calculations come in. I'd have to get rid of the title, or input a constant range for each function.

in "statistics" column E starting from E6 to E29 are Hours 12:00 AM through 11:00 PM typed in that format.

The function i'm trying to write goes in F6 to F29 and what it does is counts all entries in "cit" that are voided, and matches the hour adjacent. This creates an hourly report, so for example, in the first cell F6, if there were any records made from 12:00 AM to 12:59 AM, F6 would contain the sum of any of those records that are voided.

Once i get this figured out, I would be able to do similar functions for "statistics" G and H, where they are to tally up records that have void=0&warning=1, void=0&warning=0 respectively.