CODEWITHSUNDEEP
excelexcel-formula
xxSithRagexx
xxSithRagexx

Reputation: 300

Adding an Expression to a Range in the Excel COUNTIF Function

Simple question that will likely end in me using a different function, but not entirely sure what would do the same command with an expression added to the comparison.

Here is my simple function:

=COUNTIF('Service Calls'!E:E,C3)

This will return if an exact match is found, however it is querying a time column that holds hours and minutes. What I want to pull is only the whole hour so that I can collect service calls per hour. How would I go about running the COUNTIF function with an added modifier such as HOUR() for each cell in the comparison? I could right this in code very quickly, but for some reason my brain goes blank when it comes to excel.

I have tried this, but obviously this will never work:

=COUNTIF(HOUR('Service Calls'!E:E),C3)

Any thoughts or direction would be great! Thanks guys and gals!

Upvotes: 0

Views: 527

Answers (1)

user4039065
user4039065

Reputation:

Try,

=sumproduct(--(hour('Service Calls'!E:E)=C3), sign(len('Service Calls'!E:E)))

The SUMPRODUCT function does not like thrown errors so if some of your data cannot have the HOUR stripped out of it, additional error control will have to be added.

SUMPRODUCT will calculate every cell you tell it to. It does not not truncate full column references to the extents of the data like a COUNTIFS function. Be wary about using full column references with SUMPRODUCT; always pare down the cell ranges to a reasonable approximation of the actual scope of the data.

If your data strictly 'holds hours and minutes' then this may be more appropriate (and more efficient).

=countifs('Service Calls'!E:E, ">="&time(C3, 0, 0), 'Service Calls'!E:E, "<"&time(C3+1, 0, 0))

This COUNTIFS solution is not appropriate with datetime values but the SUMPRODUCT will peel out the hour of values containing both a date and time.

Upvotes: 3

Related Questions