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]

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.

Excel 2007 allows whole columns in SumProduct and in Sum/If formulas...

Hence:

F6:

=SUMPRODUCT(
   --ISNUMBER(cit!G:G),
   --(HOUR(cit!G:G)=HOUR(E6)),
   --(cit!E:E=1),
   --(cit!F:F=1))

should yield a count.

 

[ToniShinobi]

Thank you so much.... You have been great help, and I understand more about excel.

I directly copied and pasted your code, but it didn't work with my data, However, i started a new excel file and input some values for Columns EFG for sheet "cit" and some time values in my statistics sheet and it worked like a charm. When I added titles to "cit" columns EFG in row 1, that's where it messes up. The ISNUMBER function isn't filtering that out.

I figured out why it still returns #VALUE!... apparently even if ISNUMBER() returns FALSE, the HOUR(cit!G1)=HOUR(E6) returns #VALUE!, so when the product is taken, #VALUE!*FALSE is #VALUE!

 

[Aladin Akyurek]

Thank you so much.... You have been great help, and I understand more about excel.

I directly copied and pasted your code, but it didn't work with my data, However, i started a new excel file and input some values for Columns EFG for sheet "cit" and some time values in my statistics sheet and it worked like a charm. When I added titles to "cit" columns EFG in row 1, that's where it messes up. The ISNUMBER function isn't filtering that out.

Are you getting a wrong result or...?

Are the "titles" in the first row not text?

 

[ToniShinobi]

I'm not getting a number because of the titles. The titles are Text, so the ISNUMERIC() function on the column title returns FALSE, but when multiplied with HOURS(cit!G1)=HOURS(E6) it returns #VALUE! because the title doesn't have HOURS()

 

[Aladin Akyurek]

I'm not getting a number because of the titles. The titles are Text, so the ISNUMERIC() function on the column title returns FALSE, but when multiplied with HOURS(cit!G1)=HOURS(E6) it returns #VALUE! because the title doesn't have HOURS()

FALSE multiplied with the evaluation of HOUR(cit!G1)=HOUR(E6) wil return 0, not #VALUE!...

 

[ToniShinobi]

I just typed in =#VALUE!*FALSE and the cell contains #VALUE!

I tried stopping the HOUR comparison for the first cell using an if statement, but i don't think i did that right either:
--IF(ISNUMBER(cit!G:G), (HOUR(cit!G:G)=HOUR(E6)), LEN(cit!G:G)*0)

 

[Aladin Akyurek]

I just typed in =#VALUE!*FALSE and the cell contains #VALUE!

Oh yeah! G1 is itself a text, applying HOUR to it gives #VALUE!...

Either switch to Table (and drop the ISNUMBER test) or invoke:

Control+shift+enter, not just enter...

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

 

[ToniShinobi]

There was an error in the code above. I got close with:
=SUMPRODUCT(
--IF(ISNUMBER(cit!G:G), (HOUR(cit!G:G)=HOUR(E6)), LEN(cit!G:G)*0)
--(cit!E:E=1))

With that I was able to produce a count even with the title in the column, but it count's all of the matches even if it's not the right hour

 

[ToniShinobi]

There was an error in that last post too, i dunno why it worked.
Here's what I have now, still not working though:
=SUMPRODUCT(IF(ISNUMBER(cit!G:G), (HOUR(cit!G:G)=HOUR(E6))*1, (LEN(cit!G:G)*0)), (cit!E:E=1))

 

[ToniShinobi]

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!