SUMPRODUCT & Time (cells containing formula)

Maybe this...

=SUMPRODUCT(--(A1:A10>=B2),--(A1:A10<B3))

If you're using Excel 2007 or later you can use this version:

=COUNTIFS(A1:A10,">="&B2,A1:A10,"<"&B3)

Why do you have these expressions in there:

B2+TIME(0,0,0)
B3+TIME(0,0,0)

That is the same as:

B2+0
B3+0

So, why are you adding 0 to the cell values?

Hello Tony,

There is not reason to use the time(0.0.0). Its, I guess the blind leading the blind. Thank you for correcting me.

I tried you suggestion =SUMPRODUCT(--(A1:A10>=B2),--(A1:A10<B3)) work fine but my data is very large and it looks like it is counting duplicates. Was hoping to use sumproduct since I have to filter several other criteria.

I also used the Contifs with same result where any time that are the same are duplicated maybe more.
In short, doing it manually, I get 134 and using the formula gives me 305. Interesting enough, when adding additional criteria still keeps the same number which does not make sense.

=SUMPRODUCT(--(AA5:AA32592<=AC6),--(AA5:AA32592>AC5),--(O5:O32592="service_desk"))

If you can, use COUNTIFS as suggested by Tony Valko as it is much faster than SUMPRODUCT and like SUMPRODUCT, COUNTIFS can take many criteria. Just give it the range and the associated criteria separated by commas. Note how Tony uses the comparative operators in the COUNTIF formula. That syntax is important.

So, it sounds like you only want to count unique entries?

Can you post a SMALL sample file and tell us what result you expect?

20 rows worth of data is plenty!

Hello Tony and others,

As a singular goal you and other team member have been able to help but as a whole my data keeps getting complicated when combining formulas as I try to include other criteria. In short, too many days have been spent and have not yielded a successful outcome. I have decided to abandon ship no reflection on you or any other member in this forum.

Thanks again,

Robert

Well. if you give no information, and expect truly valid answers..... Tony's answers gave what you asked, just because you can not combine them the way you want is no reason to "abandon ship", I'm sure Tony ( or others) would help if you gave the parameters actually needed...

I'd like to help but I have a download file size limit of no more than 50kb.

I took it that the helper column had all the employees and that the Agent column was a mix of employees and non employees. Using that information, I inserted a column and entered a formula to determine which Agents were employees.

I created a column that took the dates and determined what shift the times fell into (12 shifts per day) and combined this with the day, month and year to create a unique serial number for each shift.

Seeing that the data was not in date order, I sorted the data into date order to get all the shifts together for each day and to make the end calculation easier to confirm....count the result in column I against the values in column E

OK, I downloaded the file.

Refresh my memory as to what you want to do.

Is it...

Count the number of unique agents during the hours of the day that are in a specific group?

What about the dates? Do you want the count for a specific date?

So we need to look in columns B, C and G?

If your real file has 33k rows of data and you want counts for all 24 hrs of the day, then these calculations will be slow and may take a couple of minutes to finish.

Originally Posted by rogrand

Need to find how many employees under the the column E (Agents) that matches column C "service_desk", with column E "Agents, matching the helper column "employee"
Between hours (1 to 2 am, 2 to 3 am,

How does this sound...

You want to know how many of the employees in column H worked in the service_desk between the hours of this and that?

Based on the data and structure in your sample file...

Use cells to hold the criteria:


J4 = some date like 1/1/2013
K4 = some hour of the day like 1:00 AM
L4 = some group like service_desk

Then, this formula entered in M4:

=SUMPRODUCT(--(INT(B5:B1439)=J4),--(HOUR(B5:B1439)=HOUR(K4)),--(C5:C1439=L4),--ISNUMBER(MATCH(E5:E1439,H5:H45,0)))

Ok, so you DO want to count only the unique agents that meet the conditions.

This array formula** entered in M4:

=SUM(IF(FREQUENCY(IF(INT(B5:B1439)=J4,IF(HOUR(B5:B1439)=HOUR(K4),IF(C5:C1439=L4,IF(ISNUMBER(MATCH(E5:E1439,H5:H45,0)),MATCH(E5:E1439,E5:E1439,0))))),ROW(E5:E1439)-ROW(E5)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Hello Tony,

There is light at the end of the tunnel after all. I inserted the formual to the spreadsheet I uploaded and worked like a charm.

However, this was 1/4 of the original due to file size upload.

I entered it to the original document as I mirrored your formula and received a message:

"The specified formula cannot be entered because it uses more levels of nesting then are allowed in the current file format."

Formula entered using the array ctrl,shift, enter

=SUM(IF(FREQUENCY(IF(INT(L5:L32592)=U5,IF(HOUR(L5:L32592)=HOUR(V5),IF(O5:O32592=W5,IF(ISNUMBER(MATCH(Q5:Q32591,Y5:Y45,0)),MATCH(Q5:Q32592,Q5:Q32592,0))))),ROW(Q5:Q32592)-ROW(Q5)+1),1))


I denoted the point in which it stops in bold (see above)

Do formula have limitation in size?

Are the files being used under the same versions of excel?, if your uploaded version was 2007 or above, there would be no nesting problem, if the original is under 2003 or earlier, or formatted as that, then the nesting level gets reached very quickly..

We're making progress!

Try this array formula** which should work in ANY version of Excel:

=SUM(IF(FREQUENCY(IF(TEXT(B5:B1439,"m/d/yyyy h")=TEXT(J4+K4,"d/m/yyyy h"),IF(C5:C1439=L4,IF(ISNUMBER(MATCH(E5:E1439,H5:H45,0)),MATCH(E5:E1439,E5:E1439,0)))),ROW(E5:E1439)-ROW(E5)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Some interesting aspects of Excel file types...

The sample file in *.xlsb (binary format) is only 49kb in size.

I created a copy in *.xls format and worked on it in Excel 2002 so I could come up with a formula that will work in ANY version of Excel. The *.xls file is 277kb in size.

Azumi,

Thank you for the input. the end result is more of number of occurrences in a time , date, group membership and specific employees. Much thanks


dredwolf,

You are correct, It seems to be using to what excel says it's a compatible mode. I am now saving it to .xlsx instead of .xls to avoid issues.


Tony,

Formula did not give me an error msg but gave me a result of " 0 " on my worksheet. On the one I submitted worked.

=SUM(IF(FREQUENCY(IF(TEXT(L5:L35874,"m/d/yyyy h")=TEXT(T5+U5,"d/m/yyyy h"),IF(O5:O35874=V5,IF(ISNUMBER(MATCH(Q5:Q35874,S5:S45,0)),MATCH(Q5:Q35874,Q5:Q35874,0)))),ROW(Q5:Q35874)-ROW(Q5)+1),1))


L5:L35874 = Time column

O5:O35874 = column contains service_desk amount others

Q5:Q35874 = column containing employees

S column = Has the list of employee that we are interested in.



******This is such a cliffhanger moment********

IF(TEXT(L5:L35874,"m/d/yyyy h")=TEXT(T5+U5,"d/m/yyyy h")

The text formats are not the same.

Hey Tony,

I fixed the suggusted syntx. Still will only show " 0 " as a result.

Here is a sample with only 9 rows and with your two formulas and expected results at the end. Should be 1. Its saved in the format I am useing.

Hope this helps finding out the issue.

Made slight tweaks to Formula 1 and the correct result is still 0.

Array entered**:

=SUM(IF(FREQUENCY(IF(TEXT(K2:K9,"m/d/yyyy h")=TEXT(S2+T2,"m/d/yyyy h"),IF(N2:N9=U2,IF(ISNUMBER(MATCH(P2:P9,R2:R9,0)),MATCH(P2:P9,P2:P9,0)))),ROW(P2:P9)-ROW(P2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

In the data there is a single row that ALMOST meets all the conditions.

Row 8:

K8: 10/1/2013 1:27:52 AM (meets the date and time criteria)
N8: service_desk (meets the group criteria)
P8: David S Anderson - does not meet the employee criteria

David S Anderson is not in the list of employees in R2:R9

So, the formula is returning the correct result of 0.

If I add David S Anderson to the employee list in R2:R9 then both formulas return the correct result of 1.

I knew we would eventually arrive at a solution!

You're welcome. Thanks for the feedback!