Forum Discussion
Excel formula assistance
=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.
=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.
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
=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.
- 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.
- 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?
So, that's only time, not datetime? Anyway, I guess PivotTable shall work.
- 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.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.
