Applying Function to Array in countifs()

0
votes

The Issue: The Time column in my table is a text value and not a time value. I need to convert it to a Time value within a countifs() function. Something like:

=COUNTIFS(TIMEVALUE(Table[Time]),">7:00:00 AM",TIMEVALUE(Table[Time]),"<=8:00:00 AM")

Obviously I cannot apply a TIMEVALUE function to an array, but I want to. How do I accomplish this? Full scenario below...

Summary: I am tracking call volumes and am using SharePoint 2016 to store my values. I have an Excel spreadsheet that is linked to the SharePoint, and another one within that same spreadsheet that converts the values. The table has the following column values:

  • Date
  • Department
  • Time
  • Agent

And a few more. Each entry in this table is a call summary. For example, The date is the date of the call, the time is the time that the call happened, etc. I use the countifs() function to count frequency of calls, departments, agents, and the 3 of them together (intermixed). Here's the main problem with it: the time values I get from SharePoint come in as text values and not time; Therefore, when I try to compare it with another time value, it compares the string values of both rather than the time.

Here's what I tried so far: I know the simplest solution would be to convert each time value and place both into a DateTime column in the SharePoint itself (which is a future goal), but I need a temporary solution until I can sift through 2000 records. I am currently using a helper table to convert the time values, but it requires that I populate it with all the values, and this seems redundant so I'm trying to get rid of it.

I placed my question above for quick reference, but my question remains: how do I apply a TIMEVALUE function to an array within a countifs function? If this is not possible, can anyone offer an alternate solution? I want to remain macro free, so preferably no macros or VBA, but if it's the only solution then I will consider it.

For reference, I am using Excel 2016 and SharePoint 2016 on Office 365. The time values I want to convert to are in [hh:mm AM/PM] format.

1
arraysexcelsharepointexcel-formulasharepoint-2016

1 Answers

0
votes

COUNTIFS function can only handle ranges, not arrays, so you can't use any functions or operations within COUNTIFS (not to modify the ranges, anyway)

You should be able to use a mathematical operation like +0 to convert the text values to real times within a SUMPRODUCT formula, as long as there are no unconvertible text values in the range, e.g.

=SUMPRODUCT((Table[Time]+0>="7:00"+0)*(Table[Time]+0<"8:00"+0))

or if you want to count for a single hour you could use HOUR function like this:

=SUMPRODUCT((HOUR(Table[Time])=7)+0)

You can add more conditions to either of these but it'll be slower than using COUNTIFS