How to sum using column with a date & time combination?

[Rhon Alvarez]

Hi Team,

Need help please!

I need to add up a column using another column with date and time combination, but I need to use date only as my criteria.

I was able to add it up, however, I still need to do a Text-to-Column operations so that the particular column should contain only the date in order to perform the sum function.

Was there any way where we don't need to do the Text-to-Column to lessen the time doing the file?

Please find attached file for your reference and kindly do the needful please; apologies for I have to delete some records to lessen the file size

Thanks in advance.

 

[vletm]

Rhon Alvarez
for cell F3 =SUMIFS(Sheet1!AI:AI,Sheet1!A:A,C3,Sheet1!AB:AB,">="&$E$2,Sheet1!AB:AB,"<"&$E$2+1)
will give same results as Your J-column.
Question: Do You really need all those columns of data in Sheet1?
... and if You would save Your file to .xlsb-format ... it would be much smaller.

 

[Peter Bartholomew]

Snap

= SUMIFS(
  data[Total Time In Hours],
  data[agent_name], SRTName,
  data[utc_start_time],">="&reportDate,
  data[utc_start_time], "<"&reportDate+1)

 

[Rhon Alvarez]

Hi

Rhon Alvarez
for cell F3 =SUMIFS(Sheet1!AI:AI,Sheet1!A:A,C3,Sheet1!AB:AB,">="&$E$2,Sheet1!AB:AB,"<"&$E$2+1)
will give same results as Your J-column.
Question: Do You really need all those columns of data in Sheet1?
... and if You would save Your file to .xlsb-format ... it would be much smaller.

Hi Vletm,

Thanks for the provided formula, it works well. With regards to your question about the several columns, unfortunately, that was the raw data generated and extracted from our client's tools. We really don't need each of it but just the columns on the formula. Your suggestion to save the file in .xslb format is highly noted.

Thanks once again.

Rhon

 

[vletm]

Rhon Alvarez
... extracted from our client's tools
Do above mean that You'll get an Excel-file somewhere?
Or
Do You extract data Yourself eg from .csv-file?
In both cases, try to get only needed columns for use.

 

[Rhon Alvarez]

Rhon Alvarez
... extracted from our client's tools
Do above mean that You'll get an Excel-file somewhere?
Or
Do You extract data Yourself eg from .csv-file?
In both cases, try to get only needed columns for use.

It's actually a tableau data being extracted and exported to excel, there's no way to extract it only by a particular column. In addition, since the tableau is relatively new to the client and they do often times updating it, columns were actually interchanging after the updates. the names now is on column A, after the update it would be on column B and even additional columns appear.

so there's no other way for us but to manually edit the formulas we have in placed on our templates. We're also on the process of trimming down the columns and pasting it as new raw data.

 

[vletm]

Rhon Alvarez
... so You mean that You need something like this for Your results? (... 31kB)
If Your client cannot do it ... then You could do it at least manually.

 

[p45cal]

Make the range on Sheet1 A1:AO6947 into a proper Excel Table with its headers. To do this, select that whole range and then on the keyboard press Ctrl+T, and tick the box My table has headers. This will make the formulae less resource-hungry avoiding whole-column references.
Sheet2 cell F3 formula:

=SUMPRODUCT(Table2[Total Time In Hours],(Table2[agent_name]=C3)*(INT(Table2[utc_start_time])=$E$2))

Copy down.
[Make a note of the table's name when you convert it and use that instead of Table2 in the formula above.]
The important bit is the INT(Table2[utc_start_time] which you can't use in SUMIFS.