COUNTIF with Multiple Criteria

I have searched all over the Internet, and I cannot find the answer to my issue. I think the solution is a COUNTIF with multiple criteria, but I'm not sure how to make it work. Nothing I have found has helped me with my specific use.

My employer has started using Excel to keep track of things, but it is very non user friendly. I have tracked my work in the past using excel, and I want to do be able to populate the new spreadsheet with my own. The two spreadsheets are set up as follows:

My Spreadsheet
Column A contains a start time. Column B contains a stop time. Column C contains a code word for the time of work you are doing. Column D calculates the number of minutes spent on that task. Fairly simple sheet.

START____STOP________CODE_____TIME
8:03 AM_____8:10 AM______Email_______7
8:15 AM_____8:30 AM______Email_______5


New Spreadsheet
Column A will contain the list of tasks. Columns to the right are broken up as follows. There will be a column that represents a 2 hour time frame. The next column to the right represents the next 2 hour time frame, etc. So let's say that in Column A, a task is labeled as Email. If you did 3 emails between 8 AM and 10 AM, then you would put a 3 in the corresponding cell in that row under the 8 to 10 column.

CODE___8 to 10____10 to 12____12 to 2
Email________3_________5___________1
Call_________2_________2___________4
Etc__________1_________2___________3


I'm trying to use my spreadsheet to auto-populate the new one. So for example, I need a formula that I can put in the New Spreadsheet, in the cell underneath the 8 to 10 column in the Email row that will count the number of times the word Email appears in My Spreadsheet in the CODE column AND the START time is equal to or greater than 8 AM and less than 10 AM. The Stop and Time column are irrelevant in this situation. So I'm dealing with 2 criteria; the number of times the word appears in a column, but also falls between a certain time frame.

I know how to use the COUNTIF function, and I was able to come up with an IF formula using AND to give me a specific result if the word appears in a cell and falls between a certain time. Formulas below. Now what I need is a way to combine them so that they encompass entire columns and counts according to specific time frames.

=COUNTIF(C4:C20,"Email")
Basic countif. Counts the number of times the word Email appears in cells C4 through C20.

=IF(AND(C4="Email",A4>=TIME(8,0,0),A4<=TIME(10,0,0)),"1","")
Basically says if Cell C4 has the word email in it, AND the time in Cell A4 is 8 AM or later, but before 10 AM, then place a 1 in the Cell, otherwise leave it blank.

I hope this isn't too convoluted. Any help will be appreciated.

Hi

For this exercise (and convenience), I've assumed that both the sheets are in the same workbook.
1) your source data is in the range sheet3!A1:D3
2) Your output headings are in the range sheet4!A1:D1
3) Your codes are in the range sheet4!A2:A4
4) Sheet4!B2: =SUMPRODUCT(--(Sheet3!$A$2:$A$3>=TIMEVALUE("08:00:00")),--(Sheet3!$B$2:$B$3<=TIMEVALUE("10:00:00")),--(Sheet3!$C$2:$C$3=Sheet4!$A2)). Copy down to B4
5) You will have to modify the timevalue criteria as you move across the columns, and work out which column you want the end times to be counted in (say 10:00 AM going into column B, or in column C.


HTH

rylo

Thanks Rylo! That's awesome! I had to tweak it just a little to fit what I was doing, but that is exactly what I was looking for. Thanks!