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.
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?
=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.