Excel formula assistance

[Tommo1105]

Countif / Countifs have issues due to the time format and pivot table options have not worked very easily either.  Looking to create a list which other workbooks can use at the same time for statistic purposes.

Copied a number of emails from outlook to count the number of emails processed during set time periods, but only way it works is a time consuming process.

Looking to use formula to group emails during the various time periods I have been asked to count.

Just not sure if there may be another formula option I have not considered.

[#2]

@Tommo1105 

=COUNTIFS($C$2:$C$9,">="&E2,$C$2:$C$9,"<="&F2)

In general you can use COUNTIFS like in the example. In the columns A to C is the information copied into Excel.

However i'm sure that the layout of your real data is different. Maybe you can attach a screenshot of your data without sensitive data and with your intended result.

[#3]

@Tommo1105 

What the entire procedure is? In general you may use Power Query to pick up emails from available mailbox(es), make the transformation and generate any metrics you wish.

If not Power Query, how do you import emails information to the Excel?

[#4]

@OliverScheurich 

Below is an example of the emails copied (ctrl c from outlook) and pasted in excel (ctrl v)  but I have modified emails and categories.  Appreciate if you can offer any suggestions to make the count process easier.  Think the issue relates to the time format as suggested COUNTIF / COUNTIFS has had problems and think it is due to the time format

[#5]

We just copy and paste the outlook data into excel (ctrl C & V process)

[#6]

@Tommo1105 

So, that's only time, not datetime? Anyway, I guess PivotTable shall work.

[#7]

No, it doesn't as we need times grouped (08:00-10:00, 10:01-12:00, etc).
Tried using a pivot table but couldn't get the columns to group for the required time periods.
Was thinking an Access option, but will need to have a go if I can't get anything to work in Excel for what I have been asked to create.

[#8]

@Tommo1105 

=COUNTIFS($C$2:$C$10000,">="&G2,$C$2:$C$10000,"<"&G3)

In Excel you can apply COUNTIFS like this. COUNTIFS dynamically updates the results when you paste new data in columns A to E. The value in cell G2 is 00:00:01 in cell G14 is 23:59:59. The other values in columns G and H are like 02:00:00.

Better option would be Power Query as suggested by @Sergei Baklan . Copy and paste option is probably limited to a few hundered mails at a time. With Power Query several thousands of mails can be imported at once.

[#9]

Will try the formula, but think the time format stopped the COUNTIFS formula working as kept getting an error when using the formula. Tried formatting time to hhmm, hh:mm and tried using hh:mm:ss but always got the error. Will try again tomorrow, so thanks for the suggestions. Will have to look into the Power Query as I have never used it or even knew about it and already linked in my excel via the Data tab.

[#10]

Tried, and managed to get a total number of emails between specific times, but still need to could categories between specific times. can a CONTIFS or COUNTIF specify the times and count a category?

[#11 · Accepted solution]

@Tommo1105 

=COUNTIFS($C$2:$C$10000,">="&$G2,$C$2:$C$10000,"<"&$G3,$E$2:$E$10000,I$1)

I'd apply COUNTIFS as shown in the screenshot.

[#12]

@Tommo1105 

Why not? Adding data to data model and creating calculating column Frame like

=FORMAT( INT(Table1[Time]*24/2)*2/24, "hh:mm") & " - " & FORMAT( 2/24 - 1e-99  + INT(Table1[Time]*24/2)*2/24, "hh:mm")

In Power Pivot that is

result will be like

It could be split by users if we have another column in the sample.

[#13]

Modified the layout to match and this and it seems to work. May just look to tidy up the display by storing the email list on another tab and looking up the data using the tab and !. Thanks and think this is the answer, though may just need to modify the time data each time, but this will make the process easier I think. Thanks for your help.